Excel 2007 : Highlight every other group of rows

Hello Jim,

lets say your data is in A1:D100.

Select that range and apply conditional formatting with the following formula

=MOD(SUM(1/COUNTIF($A$1:$A1,$A$1:$A1)),2)

apply formatting as required

Ah, sorry Jim, yes I tested it further and I got the same scenario, problem is with some small "rounding errors" with MOD which throw the calculation out. As you have Excel 2007 you can use this version which utilises ISODD function

=ISODD(SUM(1/COUNTIF($A$1:$A1,$A$1:$A1)))

Note: that won't work in Excel 2003 or earlier because ISODD, in those versions, is an Analysis ToolPak add-in function that can't be used in conditional formatting. If you want a version compatible with Excel 2003 then you can add in a ROUND function to my original suggestion, i.e.

=MOD(ROUND(SUM(1/COUNTIF($A$1:$A1,$A$1:$A1)),0),2)

Aces! This was the solution...many thanks!

I implemented using:

=ISODD(SUM(1/COUNTIF($A$1:$A1,$A$1:$A1)))

And it *looks* like it worked (I have almost 900 rows), but it took about 15 minutes to get all the highlighting done and now when I try to scroll or do anything, Excel suddenly grabs about 98% of my cpu and won't respond.

Thoughts?

Can someone help explain the rounding issues?

ISODD worked for me when MOD did not,

but on another tab, using the same formula, it gets wrong after a few rows.

Is there something more "precise?" than ISODD?

I have the same problem, except that instead of column A the data that the cells will be grouped by is in column C. i tried just replacing all the A's with C's but that didn't work. Is anyone able to explain this formula?

Thanks