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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

GlennUK

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

ADVERTISEMENT

Try my suggestion.
 

GlennUK

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

ADVERTISEMENT

What was the active cell when you applied the formatting?
 

GlennUK

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,031
Messages
5,856,936
Members
431,841
Latest member
jaybeem

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top