It's not working. It works if I manually type a number in a cell, but it
doesn't work where all my VLOOKUP formulas are. I tried re-entering my
formulas, recalculating, and even copying and just pasting the values. Oh,
and I did change one thing. I needed this to work for all of column A, not
just rows 1 - 10, so I typed

If Not Intersect(Target, Range("A:A")) Is Nothing Then (etc...)

I keep getting "Run-time error '13': Type mismatch". If I click "Debug"
then the debugger stops on Case 1.

"Per Jessen" wrote:

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