Assign unique colors for each cell in a range

[krishnakashyap]

I have a range in excel A1:C2 I have a cell with a value which would repeat in other locations of the range and would like to assign unique color to these cells in the range. Similarly for other values in the range.

The worksheet would look something like the attached once it is solved.

I found a script which isn't exactly working for my range. Thanks a lot for your attention and any help will be appreciated.

Please Login or Register  to view this content.

[FDibbins]

Hi, welcome to the forum

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

I will add them for you - this time.

Your question is confusing, and your file doesnt help much either (well, to me, anyway)

Are you trying to color all cells that do not duplicate (per your heading), or all cells that match some other cell (per your post)?

[krishnakashyap]

Thanks for your reply FDibbins and apology for not following rule 3. I shall take care of it in my future posts.

Going back to my question, as you can see in the workbook, Range A1:C2 has three (viz, ABC, CCC & RRR) types of values in it. My requirement is to have one color for cells with values ABC, another color for CCC and a different color for RRR throughout the range. It should assign unique color to a cell in a range. Hope this helps.

[Trebor76]

Hi krishnakashyap,

Welcome to the forum and thanks for wrapping your with the appropriate tags as Ford as requested.

This code will colour code each unique item, though the colour it gives for each unique item will be different each time you run it:

Please Login or Register  to view this content.

Regards,

Robert

[krishnakashyap]

Thanks a lot for the reply Trebor76.

Before I could mark this thread as solved, I had one last requirement. The above code works great, and I had to extend this code for an additional requirement wherein code has to assign unique colors based on the first three letters of the cells.
Attached is the example. I greatly appreciate your help and time.

Thanks again.
Regards,
Krishna

[Trebor76]

Hi Kirshna,

I had to extend this code for an additional requirement wherein code has to assign unique colors based on the first three letters of the cells.

There's not a lot to change:

Please Login or Register  to view this content.

I did add a range variable called "rngMyRange" so you can set the range once.

Regards,

Robert