Try this. Adjust colors to suit from color palette numbers.
Sub docolor()
For Each c In Range("a11:a16")
x = Application.Choose(c, 3, 46, 6, 4, 5, 13)
c.Interior.ColorIndex = x
c.Font.ColorIndex = x
Next c
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jjones" wrote in message
...
Column A of my spreadsheet contains a VLOOKUP formula all the way down
that
returns one of six numerical answers (i.e. the number 1, 2, 3, 4, 5, or
6).
I want these numbers there for sorting purposes, but I don't want to
actually
see them. Instead I want to see a "color code" all the way down. So if
the
value is 1, then I want the background color and the font for that cell to
be
red. If 2, then orange, etc...

I know that conditional formatting limits me to 3 conditions, but I'm sure
that I can write some sort of CASE statement to do the same thing. I've
found several posts similar to what I'm looking for, but not exact. I
tried
to piece them together, but since my VB skills leave alot to be desired, I
need some help to pull this off. What I have is something like this:
__________________________________________________ ____

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer

If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Select Case Target
Case 1
Font.ColorIndex = 3
icolor = 3
Case 2
Font.ColorIndex = 46
icolor = 46
Case 3
Font.ColorIndex = 6
icolor = 6
Case 4
ColorIndex = 4
icolor = 4
Case 5
Font.ColorIndex = 5
icolor = 5
Case 6
ColorIndex = 13
icolor = 13
Case Else
'Whatever
End Select

Target.Interior.ColorIndex = icolor
End If

End Sub
__________________________________________________ ____

It doesn't seem to do anything. Can someone tell me how this code should
be
written?

Thanks in advance,
JJ