VLOOKUP in a conditional format

[Guest]

VLOOKUP a number and fill a cell with a color that equals the number. i
require more than three colors so i can't use the 'ADD CONDITION' in
conditional formating
eg 1= red
2= green

if 1 is entered in a cell, then fill that cell in red

 

[L. Howard Kittle]

Hi Russell,

I have a worksheet that has a drop down list of Red, Green, Blue, and if you
pick the color from the list it turns the cell that color. It can be can
modified to return any color to the any cell of choice after the selection.

HTH
Regards,
Howard

 

[Guest]

Thanks L.Howard Kittle,
That's not quite what i'm looking for. I have a legend (key) to rate my
product by numbers 1~6
1 = red, 2= green etc. if i put a '1' as a rating I want the cell to lookup
'1' and return it's corresponding color and fill that cell.

 

[L. Howard Kittle]

Hi Russell,

I believe this will do what you are asking. Where the 1 to 6 dropdown is in
F1. Change the Target.Column and Target.Row to suit. Change the color
index to suit/match your 1 - 6 colors.

If you want to still see the number that was selected delete these lines
below.
Target.Font.ColorIndex = 3


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 6 Then Exit Sub
If Target.Row <> 1 Then Exit Sub
Target.Interior.ColorIndex = xlNone

If Range("F1").Value = 1 Then
With Target.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Target.Font.ColorIndex = 3

ElseIf Range("F1").Value = 2 Then
With Target.Interior
.ColorIndex = 41
.Pattern = xlSolid
End With
Target.Font.ColorIndex = 41

ElseIf Range("F1").Value = 3 Then
With Target.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
Target.Font.ColorIndex = 4

ElseIf Range("F1").Value = 4 Then
With Target.Interior
.ColorIndex = 46
.Pattern = xlSolid
End With
Target.Font.ColorIndex = 46

ElseIf Range("F1").Value = 5 Then
With Target.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Target.Font.ColorIndex = 6

ElseIf Range("F1").Value = 6 Then
With Target.Interior
.ColorIndex = 38
.Pattern = xlSolid
End With
Target.Font.ColorIndex = 38

End If
End Sub

HTH
Regards,
Howard