Conditional colours in Lookup table

  • Thread starter Thread starter @Homeonthecouch
  • Start date Start date
@

@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
 
Have you looked at Conditional formatting?

In xl2003 menus:
Format|conditional formatting
 
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
 
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
 
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
 
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
 
Back
Top