Highlight Rows (Alternating Colors) Based on Cell Value

[UzieJacuzzi]

All, I have values in Column H in a file that could be big or large (so the # of rows is unpredictable)

Data could be something like:

1
2
2
3
4
4
4
4
5
5
6

I'd like to do the following:

• Highlight all consecutive duplicates as one color, and extend highlight to the entire row, not just that cell
• Using the above example, When switching from 2 to 3, the color should change.
• It could be white and gray, It doesnt matter.
• Note: I would like the cell fill to be white/gray, not the text color.

Thanks in advance! I am not sure if conditional formatting can be used for this or if I can use a macro instead...

 

[Andrew Poulsom]

If your data is in the range A1:A11 you can use the following conditional formatting formula:

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