Shade Similar Rows Based On Value

hutchisd

New Member
Joined
Feb 16, 2009
Messages
49
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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
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.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402

ADVERTISEMENT

Try my suggestion.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402

ADVERTISEMENT

What was the active cell when you applied the formatting?
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
Select A2 now ... go into Conditional Formatting and look at the formula, then copy it into your next reply.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,033
Messages
5,526,363
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top