Shade Similar Rows Based On Value

[hutchisd]

I have a list of 16,000 part numbers with 3,500 unique part numbers. I want a macro that will highlight the rows of all matching part numbers. I have seen examples of this task with a case statement but I would need 3,500 cases in order for this to work. I don't really care what the colors are or if they repeat, as long as two back-to-back part numbers don't have the same color.

For example:

Part ABC Color 1
Part ABC Color 1
Part ABC Color 1
Part DEF Color 2
Part DEF Color 2

 

[SteveO59L]

Couldnt you just alternate the colours?

 

[GlennUK]

Conditonal Formatting with a condition, Formula Is, of:
=MOD(ROUND(SUM(1/COUNTIF($B$2:$B2,$B$2:$B2)),0),2)=1
... assuming your selection starts in cell B2. Adjust for your case.

 

[hutchisd]

Couldnt you just alternate the colours?

Sure that would be fine too but I need to automate this instead of repeating this 3,500 times.

 

[GlennUK]

Try my suggestion.

 

[hutchisd]

Try my suggestion.

For some reason it's not highlighting all cells that are the same. It highlights all but the last cell in a group.

 

[GlennUK]

What was the active cell when you applied the formatting?

 

[hutchisd]

What was the active cell when you applied the formatting?

My data is in column A with a header. I had A2 selected.

 

[GlennUK]

Select A2 now ... go into Conditional Formatting and look at the formula, then copy it into your next reply.

 

[hutchisd]

Select A2 now ... go into Conditional Formatting and look at the formula, then copy it into your next reply.

A2 is =MOD(ROUND(SUM(1/COUNTIF($A$2:$A3,$A$2:$A3)),0),2)=1