Now we're getting somewhere! :) Just one glitch. It doesn't seem to execute
automatically. I right-clicked on the sheet and clicked on "View Code".
That's where I have your code pasted. The cells don't change colors unless I
go back in to this VB screen and click the little "play" button to run the
code. Can't this fire on it's own?

"Don Guillett" wrote:

Don't use the whole column and do use the on error statement. As written, it
is only looking up 1,2,3,4,5,6

Sub docolor()
On Error Resume Next
lr = Cells(Rows.Count, "a").End(xlUp).Row
For Each c In Range("a1:a" & lr)
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
...
Hi Don

You seem to have taken a different approach to my problem. I'm intrigued
but I don't really understand what your code is saying. I did expand the
range to include all of column A...maybe I screwed it up when I did that.
I
entered:

Sub docolor()
For Each c In Range("a:a")
x = Application.Choose(c, 3, 46, 6, 4, 5, 13)
c.Interior.ColorIndex = x
c.Font.ColorIndex = x
Next c
End Sub

It does seem to respond to my VLOOKUP cells, but all I get is green (color
code 4). Was I supposed to add something else?

"Don Guillett" wrote:

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