Differentiate each set of duplicates with different fill color

Excel 2007
I have a list of values containing multiple sets of duplicates. I want to format each set of duplicates with a random color and leave the unique values alone.

Desired Result:
Capture.PNG

Ex.
apple
orange
banana
apple
pear
grape
grape

the fill color for both apples would be purple
orange, banana, pear would be left alone
the fill color for both grapes would be brown



Currently, I'm using the code below which stores all values in a dictionary and assigns each a color, making sure to add a value to the dictionary only once. This assigns a color to the unique values, however.


I coupled the code with a conditional format to find all the unique values and set them to "no fill" but this interfered with my alternating row colors in the table.

Code originally from: http://stackoverflow.com/questions/1...alues-in-excel

Please Login or Register  to view this content.

Great! Thank you. It works, but only if the data is formatted as range, rather than table, which is what I need. What modifications do I need to make in order for this code to function within a table?

I really appreciate your help!