thanks Chip - that did get the colouring working however it colours columns K
to U (11-21)
would altering .Resize(1, 11).Interior.ColorIndex = 25 to
..Resize(A, K).Interior.ColorIndex = 25 work?

thanks in advance

"Chip Pearson" wrote:

Peta,

As written, it is quite likely that you'll not get the correct matches
because you're doing vbBinaryCompare text comparisons, in which upper/lower
case does matter (e.g, "ABC" not equal "abc"). You can do one of (at least)
two things to remedy this. One is to put "Option Compare Text" on a line of
code immediately following the "Option Explicit" line. This will force all
text comparisons in the module to be case insensitive ("ABC"= "abc"). The
other way is to rewrite your Select Case statements as follows:

Select Case UCase(.Value)
Case "YES" : .Resize(1, 11).Interior.ColorIndex = 25
Case "NO" : .Resize(1, 11).Interior.ColorIndex = 2
Case "MAYBE" : .Resize(1, 11).Interior.ColorIndex = 2
End Select

This will convert the UPPER CASE .Value to UPPER CASE test values.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"Peta" wrote in message
...
Hi Mike

Yes the code is definitely in the correct worksheet.

I've also tried another suggestion I found on another thread:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Column = 11 Then
Select Case .Value
Case "YES": .Resize(1, 11).Interior.ColorIndex = 25
Case "NO": .Resize(1, 11).Interior.ColorIndex = 2
Case "Maybe": .Resize(1, 11).Interior.ColorIndex = 2
End Select
End If
End With
ws_exit:
Application.EnableEvents = True
End Sub


Also won't colour row

"Mike" wrote:

Peta,

There's nothing wrong with the code, where have you put it? You should
have
right clicked the sheet tab - view code and pasted it in there.

Mike

"Peta" wrote:

Hi - can anyone help please
I've tried Subject: More than 6 conditional Formats....VBA Coding
Advise
please 1/5/2006 7:08 PM PST

By: JulieD In: microsoft.public.excel.newusers

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Err_Handler

If Not Intersect(Target, Range("K1:K20")) Is Nothing Then
Application.EnableEvents = False
i = Target.Row
Select Case Target.Value
Case "Yes"
Range("A" & i & ":M" & i).Interior.ColorIndex = 6
Case "No"
Range("A" & i & ":M" & i).Interior.ColorIndex = 12
Case "W"
Range("A" & i & ":M" & i).Interior.ColorIndex = 18
Case "X"
Range("A" & i & ":M" & i).Interior.ColorIndex = 22
Case "Y"
Range("A" & i & ":M" & i).Interior.ColorIndex = 26
Case "Z"
Range("A" & i & ":M" & i).Interior.ColorIndex = 30
Case Else
Range("A" & i & ":M" & i).Interior.ColorIndex = xlNone
End Select
End If

Err_Handler:
Application.EnableEvents = True
End Sub

but doesn't work - I'm using XP - Excel 2003
it won't even debug - step into

any suggestion welcome
thanks