On 18 Dec., 23:51, jjones wrote:
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

Hi JJ

End Sub have to be the last line in your macro. See other changes too.

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
Target.Font.ColorIndex = 3
icolor = 3
Case 2
Target.Font.ColorIndex = 46
icolor = 46
Case 3
Target.Font.ColorIndex = 6
icolor = 6
Case 4
Target.Font.ColorIndex = 4
icolor = 4
Case 5
Target.Font.ColorIndex = 5
icolor = 5
Case 6
Target.Font.ColorIndex = 13
icolor = 13
Case Else
'Whatever
End Select


Target.Interior.ColorIndex = icolor
End If

End Sub

Regards,
Per