Excel 2007 : Highlight every other group of rows

[JimDandy]

I’m trying to take a table of duplicate information and highlight every other row with a light grey highlight just so it’s easier to see the individual groups. The duplicate data is sorted and in column A and the other columns will have varying bits of data. sometimes there are 2 duplicate values and sometimes there are 3, maybe even 4.

In the example below, the rows with 12345 data should be light gray along with the 98765 rows

12345	data1	data2	data3
12345	data3	data2	data1
54321	data1	data2	data3
54321	data4	data5	data6
54321	data7	data6	data5
98765	data1	data5	data2
98765	data3	data1	data8

[daddylonglegs]

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

[JimDandy]

It looks like it tries to highlight the rows but after the first couple groups, it stops getting accurate about the grouping.

[daddylonglegs]

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)

[JimDandy]

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

[scriptThis]

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?

[Sako]

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?

[FDibbins]

sako...Welcome to the Forum.

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[Fiona U]

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

[FDibbins]

Fiona, please read what I posted in post 8...right above your post!