Hello,
I have a table that reports results from a lookup table.
The results are in column "L"
I am wanting specifics to be highlighted.
If a cell reports "sam" or "jim" to be blue etc.

Thanks in advance

Andrew

Have you looked at Conditional formatting?

In xl2003 menus:
Format|conditional formatting

"@Homeonthecouch" wrote:
>
> Hello,
> I have a table that reports results from a lookup table.
> The results are in column "L"
> I am wanting specifics to be highlighted.
> If a cell reports "sam" or "jim" to be blue etc.
>
> Thanks in advance
>
> Andrew

--

Dave Peterson

Yeah I didn't explain that I want to use more than 3 colours and require the VB script did I?

So the conditional formatting doesn't offer enough choice.

Again Any help is appreciated.

Andrew

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message news:4824777D.8F038A0@verizonXSPAM.net...
Have you looked at Conditional formatting?

In xl2003 menus:
Format|conditional formatting

"@Homeonthecouch" wrote:
>
> Hello,
> I have a table that reports results from a lookup table.
> The results are in column "L"
> I am wanting specifics to be highlighted.
> If a cell reports "sam" or "jim" to be blue etc.
>
> Thanks in advance
>
> Andrew

--

Dave Peterson

Try this event code.

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A20")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
On Error GoTo Endit
Application.EnableEvents = False
vals = Array("Cat", "Dog", "Gopher", "Hyena", "Ibex", "Lynx", _
"Ocelot", "Skunk", "Tiger", "Yak")
nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 23, 15)
For Each rr In r
icolor = 0
For i = LBound(vals) To UBound(vals)
If rr.Value = vals(i) Then
icolor = nums(i)
End If
Next
If icolor <> 0 Then
rr.Interior.ColorIndex = icolor
End If
Next
Endit:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code".. Copy/paste to that sheet module.

Edit then Alt + q to freturn to the Excel window.

As an alternative.............Bob Phillips has a CFPLUS add-in that allows up to
30 CF's in a cell.

http://www.xldynamic.com/source/xld.....Download.html


Gord Dibben MS Excel MVP

On Sat, 10 May 2008 01:21:17 +0100, "@Homeonthecouch" <me@home.com> wrote:

>Yeah I didn't explain that I want to use more than 3 colours and require the VB script did I?
>
>So the conditional formatting doesn't offer enough choice.
>
>Again Any help is appreciated.
>
>Andrew
>
>"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message news:4824777D.8F038A0@verizonXSPAM.net...
>Have you looked at Conditional formatting?
>
>In xl2003 menus:
>Format|conditional formatting
>
>"@Homeonthecouch" wrote:
>>
>> Hello,
>> I have a table that reports results from a lookup table.
>> The results are in column "L"
>> I am wanting specifics to be highlighted.
>> If a cell reports "sam" or "jim" to be blue etc.
>>
>> Thanks in advance
>>
>> Andrew

Thanks Gord,

That works a treat, I tried the download from the link too but wasn't happy with it.
If I wanted to also make the text bold and white could this be added into the event code you submitted?

Once again thanks for your help already.

Andrew


"Gord Dibben" <gorddibbATshawDOTca> wrote in message news:rar924pjljm08vm5167odoa4lc1t653g6a@4ax.com...
Try this event code.

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A20")
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
On Error GoTo Endit
Application.EnableEvents = False
vals = Array("Cat", "Dog", "Gopher", "Hyena", "Ibex", "Lynx", _
"Ocelot", "Skunk", "Tiger", "Yak")
nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 23, 15)
For Each rr In r
icolor = 0
For i = LBound(vals) To UBound(vals)
If rr.Value = vals(i) Then
icolor = nums(i)
End If
Next
If icolor <> 0 Then
rr.Interior.ColorIndex = icolor
End If
Next
Endit:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code".. Copy/paste to that sheet module.

Edit then Alt + q to freturn to the Excel window.

As an alternative.............Bob Phillips has a CFPLUS add-in that allows up to
30 CF's in a cell.

http://www.xldynamic.com/source/xld.....Download.html


Gord Dibben MS Excel MVP

On Sat, 10 May 2008 01:21:17 +0100, "@Homeonthecouch" <me@home.com> wrote:

>Yeah I didn't explain that I want to use more than 3 colours and require the VB script did I?
>
>So the conditional formatting doesn't offer enough choice.
>
>Again Any help is appreciated.
>
>Andrew
>
>"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message news:4824777D.8F038A0@verizonXSPAM.net...
>Have you looked at Conditional formatting?
>
>In xl2003 menus:
>Format|conditional formatting
>
>"@Homeonthecouch" wrote:
>>
>> Hello,
>> I have a table that reports results from a lookup table.
>> The results are in column "L"
>> I am wanting specifics to be highlighted.
>> If a cell reports "sam" or "jim" to be blue etc.
>>
>> Thanks in advance
>>
>> Andrew

Make changes as such...............

Next
If icolor <> 0 Then
With rr
.Interior.ColorIndex = icolor
.Font.ColorIndex = 2
.Font.Bold = True
End With
End If
Next


Gord

On Sat, 10 May 2008 08:08:19 +0100, "@Homeonthecouch" <me@home.com> wrote:

>Thanks Gord,
>
>That works a treat, I tried the download from the link too but wasn't happy with it.
>If I wanted to also make the text bold and white could this be added into the event code you submitted?
>
>Once again thanks for your help already.
>
>Andrew
>
>
>"Gord Dibben" <gorddibbATshawDOTca> wrote in message news:rar924pjljm08vm5167odoa4lc1t653g6a@4ax.com...
>Try this event code.
>
>Option Compare Text
>Private Sub Worksheet_Change(ByVal Target As Range)
>Set r = Range("A1:A20")
>If Intersect(Target, r) Is Nothing Then
> Exit Sub
>End If
>On Error GoTo Endit
>Application.EnableEvents = False
>vals = Array("Cat", "Dog", "Gopher", "Hyena", "Ibex", "Lynx", _
>"Ocelot", "Skunk", "Tiger", "Yak")
>nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 23, 15)
>For Each rr In r
> icolor = 0
> For i = LBound(vals) To UBound(vals)
> If rr.Value = vals(i) Then
> icolor = nums(i)
> End If
> Next
> If icolor <> 0 Then
> rr.Interior.ColorIndex = icolor
> End If
>Next
>Endit:
>Application.EnableEvents = True
>End Sub
>
>Right-click on the sheet tab and "View Code".. Copy/paste to that sheet module.
>
>Edit then Alt + q to freturn to the Excel window.
>
>As an alternative.............Bob Phillips has a CFPLUS add-in that allows up to
>30 CF's in a cell.
>
>http://www.xldynamic.com/source/xld.....Download.html
>
>
>Gord Dibben MS Excel MVP
>
>On Sat, 10 May 2008 01:21:17 +0100, "@Homeonthecouch" <me@home.com> wrote:
>
>>Yeah I didn't explain that I want to use more than 3 colours and require the VB script did I?
>>
>>So the conditional formatting doesn't offer enough choice.
>>
>>Again Any help is appreciated.
>>
>>Andrew
>>
>>"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message news:4824777D.8F038A0@verizonXSPAM.net...
>>Have you looked at Conditional formatting?
>>
>>In xl2003 menus:
>>Format|conditional formatting
>>
>>"@Homeonthecouch" wrote:
>>>
>>> Hello,
>>> I have a table that reports results from a lookup table.
>>> The results are in column "L"
>>> I am wanting specifics to be highlighted.
>>> If a cell reports "sam" or "jim" to be blue etc.
>>>
>>> Thanks in advance
>>>
>>> Andrew
>