Conditional colours in Lookup table

[@Homeonthecouch]

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]

Have you looked at Conditional formatting?

In xl2003 menus:
Format|conditional formatting

 

[@Homeonthecouch]

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

Have you looked at Conditional formatting?

In xl2003 menus:
Format|conditional formatting

 

[Gord Dibben]

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.CFPlus.Download.html


Gord Dibben MS Excel MVP

 

[@Homeonthecouch]

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 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.CFPlus.Download.html


Gord Dibben MS Excel MVP

 

[Gord Dibben]

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