• 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

    Hui...

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

ColorFunction

Lugznut

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?
 

simcat95

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.
 

Hui

Excel Ninja
Staff member
Lugznut


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

Next

End If


End Sub


==================

You can have a browse through this example

http://rapidshare.com/files/368675165/ColorFunction.xls
 

Lugznut

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.
 
Top