VLOOKUP in a conditional format

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

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

"Russell Brown" <Russell Brown@discussions.microsoft.com> wrote in message
news:EEF45D86-F5FF-41B4-96A3-E893E9E1BFEA@microsoft.com...
> 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

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" wrote:

> 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
>
> "Russell Brown" <Russell Brown@discussions.microsoft.com> wrote in message
> news:EEF45D86-F5FF-41B4-96A3-E893E9E1BFEA@microsoft.com...
> > 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
>
>
>

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

"Russell Brown" <Russell Brown@discussions.microsoft.com> wrote in message
news:EEF45D86-F5FF-41B4-96A3-E893E9E1BFEA@microsoft.com...
> 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