Automatically have even numbered rows shaded gray?
 

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!!

Automatically have even numbered rows shaded gray?
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"phillr" wrote in message
...
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!!

Automatically have even numbered rows shaded gray?
 

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.

--
Thanks,
Shane Devenshire


"Jon Peltier" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"phillr" wrote in message
...
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!!