Conditonal Formatting


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 : )



Selected Answer

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.



Wow, this is pretty incredible. I could do something with another sheet of mine. 

Maybe we can forget about multiple colors and just do alternating colors and apply it all the way down. Say light blue. Then white. But stick with the same rules in column B as the unique identifier. 

Thank you so so much!!!!
Sroncey21 (rep: 62) Dec 17, '18 at 12:09 pm
Sorry S, this goes against my grain. If everything is coloured, like your original idea, you can see nothing. Useless.
OK, so you only have half a page and its much better if the boss sees nothing (lol:), but if you give him alternative colours, say light blue and white, he will see even less than nothing because he can't ever tell which blue is the duplicate of which other blue. How many unique identifiers could there possibly be on a real page? How many rows maximum? Are you really and finally determined to colour up everything - just leave those rows white which have no duplicate? Would it do to mark all duplicates blue and unique items white? Was that your suggestion?
Variatus (rep: 3128) Dec 17, '18 at 8:06 pm
I've attached "Example 2" sheet and want to know how to conditional format based on the Policy Number in Column G every time there is a duplicate to group and color them based on the same work type in column B. Just a simple color alternation from Blue to white. : )
Sroncey21 (rep: 62) Dec 17, '18 at 10:19 pm
P.S. Love the highlight rule you applied on the first sheet! I am going to utilize that if I can figure out how to apply your VBA code to my other sheets! Trying to figure out what to replace in your code to make it work : )
Sroncey21 (rep: 62) Dec 18, '18 at 12:22 am
Hi S, I don't understand "format based on the Policy Number in Column G every time there is a duplicate to group and color them based on the same work type in column B. Just a simple color alternation from Blue to white". No room here to explain. Please put a better formulation in a new question.
In this thread, let's deal with the answer already provided. To implement the solution, copy and paste the code to the code module of the worksheet on which you want the action. Do you know how to do that?
At the top of the code you see "Const RefClm As Long = 2 ". This specifies column 2 (=B). Change as required. The code will look for duplicates in that column (only that column) and highlight all occurrances.
The code presumes that you have captions in row 1 and that your data start in column A. If this isn't the case please post a copy of your worksheet and I shall adapt the code for you.
Variatus (rep: 3128) Dec 18, '18 at 4:12 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login