Changing Colors of Alternating Rows

**gss** · 03-23-2009, 02:31 PM

How do I change the colors of every other row that has values in it?

i.e.

I have values in rows 1 through 34. I want rows 2,4,6, etc to change to a light green color.

Thanks

**NBVC** · 03-23-2009, 02:33 PM

Select entire range from rows 1 to 34 and go to Format|Conditional formatting:

Select Formula Is from 1st drop down and enter formula: =MOD(ROW(),2)=1

Then click format to choose your colour pattern

If you want to start highlighting with Row 2, then use: =MOD(ROW(),2)=0 instead (after selecting from row 1 down).

**gss** · 03-23-2009, 03:24 PM

That works, but the spreadsheet I have uses macros to create a new tab when a button is pressed. And the new tab can have values in any given number of rows depending on the input values in the original page. So one run could have values in 20 rows. And another run can put values in 40 rows. I want every other row highlighted only up to as many rows have values in it for THAT run.

Edit: I am using 2007. I believe the previous response was for 2003.

**NBVC** · 03-23-2009, 03:30 PM

Then you will need to highlight all columns that are contained in the rows and edit your CF formula to:

=AND($A1<>"",MOD(ROW(),2)=0)

where it is assumed that the row should follow the conditional format as long as there are values in column A.