• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.



New Member
I have a table that list 160 paint colors with their own id number 1-150.

6 Supplers for those colors.

Each supplier cell background is color coded.

When I eneter a paint number in the worksheet I want the cell's background to change and match the correct suppiler.

Column = 1-50 colors

Column 2 - each of those colors has the corresponding supplier adjacent to their respective colors.

Is it possible that when a color number is entered into the cell that you can have it find the correct supplier in column 2 and change the background color of the input cell to match?


New Member
You can do conditional format. Not sure if you have tried that already. I believe you can only specify four conditions in conditional formatting.

You can definitely do more than 4 in VBA.


Excel Ninja
Staff member

I assume you have 2 columns with numbers 1-50 or 150 which are colored and an adjacent cell which has the suppliers name

I have named the Colored Cells "Colors"

Where ever it is you want to type in the suppliers Number, name that cell InputCell

Copy the following code to a worksheet code page for the page your colors are on


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("InputCell").Address Then

For Each c In Range("Colors")

If c.Value = Range("InputCell").Value Then Range("InputCell").Interior.ColorIndex = c.Interior.ColorIndex


End If

End Sub


You can have a browse through this example



New Member
Hui - This is exactly what I was trying to accomplish. I have tried with conditional formatting but my method kept coming up short. Thank you folks for helping an old man out.