Terrific - thanks a lot

"Dave Peterson" wrote:

There were dots in front of those suggested .offset() lines. Those dots meant
that they belonged to something--either the object right in front of them--like:

Target.offset(.....

Or they could belong to the object in the previous With statement:

Option Explicit
Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Me.Range("E:E")) Is Nothing Then Exit Sub

On Error GoTo Err_Handler
Application.EnableEvents = False
With Target
Select Case LCase(.Value)
Case "commenced":
.Offset(0, -4).Resize(1, 7).Interior.ColorIndex = 25
Case "pending":
.Offset(0, -4).Resize(1, 7).Interior.ColorIndex = 12
'other cases in here
Case Else
.Offset(0, -4).Resize(1, 7).Interior.ColorIndex = xlNone
End Select
End With

Err_Handler:
Application.EnableEvents = True
End Sub

Peta wrote:

Dave I'm still getting errors

Sub Worksheet_Change(ByVal target As Range)
On Error GoTo Err_Handler
If target.Address < "E" Then Exit Sub

If Not (Intersect(target, Range("E:E"))) Is Nothing Then
Application.EnableEvents = False

Select Case target.Value
'Select Case (.Value) 'invalid or unqualified reference
Case "commenced":
Offset(0, -4).Resize(1, 7).Interior.ColorIndex = 25
'with .Offset... gives error - "invalid or unqualified reference" - (with a
period)
'without a period - as above - gives error: "sub or function not defined"
Case "pending":
Offset(0, -4).Resize(1, 7).Interior.ColorIndex = 12
'other cases in here
Case Else
Offset(0, -4).Resize(1, 7).Interior.ColorIndex = xlnone
End Select
End If
Err_Handler:
Application.EnableEvents = True
End Sub

thanks for your patience

regards
peta

"Dave Peterson" wrote:

..resize() expects numbers (number of rows, number of columns)

There's another property that you can use to specify where to start.
..Resize(1, 11).Interior.ColorIndex = 25
becomes
..offset(0,-10).Resize(1, 11).Interior.ColorIndex = 25

Since you're looking at column K, then .offset(0,-10) says to stay on that same
row (with the 0), but go 11 columns to the left (-11).

Then the .resize kicks in: Make the shaded range 1 row by 11 columns.


Peta wrote:

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




--

Dave Peterson


--

Dave Peterson