Automatically have even numbered rows shaded gray?

[phillr]

I have a big long spreadsheet (great description!). Right now i'm using the
AutoFormat option to make every other row (e.g. rows 2, 4, 6, 8, etc...) have
a color shade of light gray.

The complication of this is:
1) Due to length and amount of data in spreadsheet, takes a LONG time to
make the change
2) Have to repeatedly re-AutoFormat as I add more information to the
spreadsheet
3) Because of some special titling at the top of the sheet, I have to
manually select a few rows down from the top, then select the bottom-most row
before AutoFormatting

I'm wondering if somebody could inform me of a way to have Excel
automatically do the gray cell shading on-the-fly as i enter data, or
automatically have every other row starting from a certain cell onwards have
gray shading.

I'm thinking this would use conditional formatting, but i'm horrible at math
and formulas. Since I need the shading to start at a certain row, please let
me know what cell number i should be inputting into your formula.

Thanks!!

 

[Jon Peltier]

Select the entire worksheet, go to Format menu > Conditional Formatting,
select Formula Is, and enter a formula like this:

=AND(ROW()>5,MOD(ROW(),2)=1)

where ROW()>5 means it will work on row numbers greater than 5.
MOD(ROW(),2)=1 means it will work on odd rows; use =0 for it to shade even
rows. Then choose a format for this condition.

- Jon

 

[ShaneDevenshire]

Hi,

If you want odd rows colored the formula is

=MOD(ROW(),2)

If you want even rows colored use

=MOD(ROW(),2)=0

These go into the conditional formatting area. Start by hightlighting the
range you want to work with.