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

 

[GlennUK]

Change the formula to be:

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

... you had changed a couple of 2s to 3s. See if that works.

 

[LCTrucido]

Change the formula to be:

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

... you had changed a couple of 2s to 3s. See if that works.

I'm using this solution as well and it's working properly, but this sheet is now extremely slow to do anything. Do you know a way to do this without the MOD function? To be fair, I have 30 columns and 500 rows that are being formatted by the data in column A

 

[chuckles1066]

You might be able to adapt this:

http://www.mrexcel.com/forum/showthread.php?t=522074

 

[LCTrucido]

Perfect, thanks.