For debugging add this function into the VBA. Your code doesn't run because
VBA doesn't recognized that Worksheet_Change requires parameter.

Main subroutines that are called from an excel spreadsheet cannot contain
parameters. Only secondarty subroutines can have parameters. subroutine
also cannot return parameters.

Functions can be have parameters and return values, but cannot write to
excel spreadsheets.

Beginners often havve these problems.

Sub abc()

Set myRange = Range("A1:D5")

Worksheet_Change (myRange)

End Sub

"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