# Shade alternating lines in Excel



## mrap1 (Apr 19, 2002)

Can someone tell me how I can shade alternating lines two different colors? I know how to do it manually, but I have this huge spreadsheet (about 900 lines long) and it would take me forever to do it one by one. So is there any quick way to make one line white and the next grey throught the entire sheet?


----------



## Rockn (Jul 29, 2001)

There are more than one way to do it, but the easiest is to select the whole range of cells toy want formatted line that and select format > auto format and select List1


----------



## cri (Sep 1, 2001)

Using Edit|Autoformat has the drawback to be static, i.e. should you have to sort/insert rows your rows then you will have to re-apply it AND _all_ other cell/font formatting too...

The most elegant way I know is to use ConditionalFormating:

a) Select your range, taking mental note of the cell address you started with, say C9 (or switch on the FormulaBar)
b) Format|ConditionalFormatting: 
Formula is =(EVEN(ROW($C9))=ROW($C9))
Format: Choose a pattern, case printout is to be faxed choose a very light one 

Remarks
- The overhead is surprisingly low, ~ 0.5 kB for 1000 x 50 cells 
- If all workbook users have ToolPak installed, then the formula can be shortened to =ISEVEN(ROW($C9)
- If you want to detect _row_ sorting errors, write (the analog of) C9 in the formula


----------



## mrap1 (Apr 19, 2002)

I think the first method is much easier, I couldn't even get the second method to work. The only draw back is that you can't choose what colors you want to use. Any idea of how to change the colors instead of black and white?


----------



## cri (Sep 1, 2001)

These Autoformats are predefined. I do not know how to alter them.
Re easier: Not always the quicker way, depends whether you will expand/sort your worksheet afterward.

If you follow the recipy by the letter it will work. Make sure that:
- Select the whole range you want to format, this also when making changes to the ConditionalFormatting.
- Write the formula correctly (in the ConditionalFormating dialog !) and check whether Excel did not transform $C9 into '$C9', which sometimes happens when making a syntax error.


----------



## jbcalg (Oct 29, 2001)

you can also use the paintbrush / format painter icon to copy the format

ie
highlight the row (by clicking on the row number), right click, format cells, patterns, choose the colour 

so, leave the first row as is (ie it's already white), then change the next row to gray

highlight both rows (click on the row number and drag down one row) , click on the paintbrush

highlight and drag down the number of rows you want 

note: this will copy and paste ALL the formating - numbers, left, right justify, etc

the other prob is the whole row gets the colour, not just the columns you're using

you can also drag and highlight across the columns you're using, then do the format painter


----------



## mrap1 (Apr 19, 2002)

Thanks jbcalg, your method was the easiest and best matched my needs.


----------



## jbcalg (Oct 29, 2001)

my pleasure 

thanks for posting back to let others know what worked for you


----------

