200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
How would you conditional format based on Unique reference in a row.
For example, in column B I want to color the row the same color all the way down if there is another match to that same reference. If you look in my worksheey I've custom colored based on what I want to do. If you see "712" in B2 and B14 colored blue. But when it hits a new reference I want it to change colors like for "CDSRETCK" in B3, B15, and B16.
I have no preference on colors - just something classical and neat : )
The devil is in the detail. Take conditional formatting. You would have to associate one particular colour with each reference. Managing these colours would prove a major task.
So you let VBA do the managing. Say, you provide 50 colours. That would mean that you wouldn't be able to have more than 50 references without repetition. So, you would have to determine the maximum number of possible references first. So, the code would loop through the entire sheet applying colours to duplicates. The result would be a very colourful sheet but if you want that you would have to specify the colours - all 50 or 150 or 250 of them.
Less is more. Since you are reconciled to using VBA, why not produce something less playful? When you click on any row, duplicates light up. This is demonstrated in the attached workbook. It's a copy of yours, with one sheet Example(2) added. The action is on this sheet and the entire code is in that sheet's code module.
I hope this solution works for you.
