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

On 18 Dec., 23:51, jjones <jjo...@discussions.microsoft.com> 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

A few revisions should help.

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

If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
With Target
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
.Font.ColorIndex = 4
icolor = 4
Case 5
.Font.ColorIndex = 5
icolor = 5
Case 6
.Font.ColorIndex = 13
icolor = 13
Case Else
'Whatever
End Select
End With
Target.Interior.ColorIndex = icolor
End If

End Sub


Gord Dibben MS Excel MVP

On Tue, 18 Dec 2007 14:51:00 -0800, jjones <(E-Mail Removed)>
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

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
(E-Mail Removed)
"jjones" <(E-Mail Removed)> wrote in message
news:FCCC2E6F-3514-4EEF-9F14-(E-Mail Removed)...
> 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

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 <jjo...@discussions.microsoft.com> 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
>

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
> (E-Mail Removed)
> "jjones" <(E-Mail Removed)> wrote in message
> news:FCCC2E6F-3514-4EEF-9F14-(E-Mail Removed)...
> > 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
>
>

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
(E-Mail Removed)
"jjones" <(E-Mail Removed)> wrote in message
news:B908B0EC-9D9D-4323-98BF-(E-Mail Removed)...
> 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
>> (E-Mail Removed)
>> "jjones" <(E-Mail Removed)> wrote in message
>> news:FCCC2E6F-3514-4EEF-9F14-(E-Mail Removed)...
>> > 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
>>
>>

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
> (E-Mail Removed)
> "jjones" <(E-Mail Removed)> wrote in message
> news:B908B0EC-9D9D-4323-98BF-(E-Mail Removed)...
> > 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
> >> (E-Mail Removed)
> >> "jjones" <(E-Mail Removed)> wrote in message
> >> news:FCCC2E6F-3514-4EEF-9F14-(E-Mail Removed)...
> >> > 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
> >>
> >>
>
>

It can be made to fire with the worksheet_calculate event but I don't
recommend it. I would assign to a shape from the drawing toolbar or a button
from the forms toolbar.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"jjones" <(E-Mail Removed)> wrote in message
news:9A7C9CE5-A61A-4B18-985A-(E-Mail Removed)...
> 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
>> (E-Mail Removed)
>> "jjones" <(E-Mail Removed)> wrote in message
>> news:B908B0EC-9D9D-4323-98BF-(E-Mail Removed)...
>> > 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
>> >> (E-Mail Removed)
>> >> "jjones" <(E-Mail Removed)> wrote in message
>> >> news:FCCC2E6F-3514-4EEF-9F14-(E-Mail Removed)...
>> >> > 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
>> >>
>> >>
>>
>>

Thanks Don. I guess I'll just make this code part of some other macros that
will be running. Appreciate the help...

--JJ

"Don Guillett" wrote:

> It can be made to fire with the worksheet_calculate event but I don't
> recommend it. I would assign to a shape from the drawing toolbar or a button
> from the forms toolbar.
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "jjones" <(E-Mail Removed)> wrote in message
> news:9A7C9CE5-A61A-4B18-985A-(E-Mail Removed)...
> > 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
> >> (E-Mail Removed)
> >> "jjones" <(E-Mail Removed)> wrote in message
> >> news:B908B0EC-9D9D-4323-98BF-(E-Mail Removed)...
> >> > 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
> >> >> (E-Mail Removed)
> >> >> "jjones" <(E-Mail Removed)> wrote in message
> >> >> news:FCCC2E6F-3514-4EEF-9F14-(E-Mail Removed)...
> >> >> > 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
> >> >>
> >> >>
> >>
> >>
>
>