Conditional formation by cell colour?

[tonywatsonhelp]

Hi Everyone,
So I have a grid, (Range B4:Z24) in each cell is a name

I also have a legend of colours (Sheets("Control").range("L3:L10") for each Name

Now I need to fill each cell with the fill colour of the name in that cell.
Dont mind if its a macro or conditional formating, but any ideas would be great thanks
Tony

 

[6StringJazzer]

There is no built-in worksheet functionality to determine the color of a cell, so you can't do it with a single conditional formatting rule. You have two options:

1. Create 8 conditional formatting rules, one for each color you have in your legend at L3:L10. Eight rules isn't too bad. You will have to update the rules any time the colors in the legend change.
2. Use VBA. If you use VBA the colors will be permanent and any time your data changes you will have to re-run the VBA. However, you do not have to change code if the colors in the legend changer. You did not give the name of the sheet containing the names so update the following code where Grid is the name and change it to the actual name.

Public Sub ColorCode()

   Dim Cell As Range
   Dim Found As Range
   
   For Each Cell In Worksheets("Grid").Range("A1:E12")
   
      Set Found = Sheets("Control").Range("L3:L10").Find(What:=Cell.Value, lookat:=xlWhole)

      If Found Is Nothing Then
         MsgBox """" & Cell.Value & """ in cell " & Cell.Address & " not found in Legend."
      Else
         Cell.Interior.Color = Found.Interior.Color
      End If
   
   Next Cell

End Sub

 

[tonywatsonhelp]

6StringJazzer, this is perfect, no problem having it rerun everytime data changes
Thanks
Tony

 

[6StringJazzer]

Very important: I left in the range I used to test. You need to update to your actual range:

   For Each Cell In Worksheets("Grid").Range("B4:Z24")