Conditional colours in Lookup table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

@Homeonthecouch - 09 May 2008 16:01 GMT

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

Reply to this Message

Dave Peterson - 09 May 2008 17:10 GMT

Have you looked at Conditional formatting?

In xl2003 menus:
Format|conditional formatting

> Hello,
> I have a table that reports results from a lookup table.
[quoted text clipped - 5 lines]
>
> Andrew

Signature

Dave Peterson

Reply to this Message

@Homeonthecouch - 10 May 2008 01:21 GMT

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

"@Homeonthecouch" wrote:

> Hello,
> I have a table that reports results from a lookup table.
[quoted text clipped - 5 lines]
>
> Andrew

Signature

Dave Peterson

Reply to this Message

Gord Dibben - 10 May 2008 01:33 GMT

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

>Yeah I didn't explain that I want to use more than 3 colours and require the VB script did I?
>
[quoted text clipped - 20 lines]
>>
>> Andrew

Reply to this Message

@Homeonthecouch - 10 May 2008 08:08 GMT

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

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

>Yeah I didn't explain that I want to use more than 3 colours and require the VB script did I?
>
[quoted text clipped - 19 lines]
>>
>> Andrew

Reply to this Message

Gord Dibben - 10 May 2008 18:01 GMT

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

>Thanks Gord,
>
[quoted text clipped - 67 lines]
>>>
>>> Andrew

Reply to this Message