Code works intermittantly is there a fault?

[Rob Hargreaves]

I am using the code below to try and make the conditional formatting use
named ranges. It works fully but intermitantly works s say I enter random
numbers only half the cells will apply to the rules in the code.

Has anyone else come accross this bizare behaiviour? I am running XP with
Office XP03

Thanks for any suggestions
Rob

Code

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

Dim icolour As Integer

Dim targ1 As Variant
Dim targ2 As Variant
Dim targ3 As Variant
Dim targ4 As Variant
Dim targ5 As Variant
Dim targ6 As Variant
Dim targ7 As Variant
Dim targ8 As Variant

'The following works just fine but I dont want to have to modify code I want
to modify a value in a cell on a page. Even if I created a user form to
alter set points I would have to store the data somewhere so it could be
saved and referenced in the code.

'targ1 = 0
'targ2 = 1700
'targ3 = 1701
'targ4 = 2200
'targ5 = 2800
'targ6 = 3000
'targ7 = 3001
'targ8 = 6000

targ1 = Range("NaburnMLSSTrig1a")
targ2 = Range("NaburnMLSSTrig1b")
targ3 = Range("NaburnMLSSTrig2a")
targ4 = Range("NaburnMLSSTrig2b")
targ5 = Range("NaburnMLSSTrig3a")
targ6 = Range("NaburnMLSSTrig3b")
targ7 = Range("NaburnMLSSTrig4a")
targ8 = Range("NaburnMLSSTrig4b")

'Half working ----- targ8 = Range("NaburnMLSSTrig4b")

'EXAMPLE FROM POST
'Range(ActiveWorkbook.Names("NaburnMLSSTrig1a")).Value

If Not Intersect(Target, Range("Y:AB")) Is Nothing Then
Select Case Target
Case targ1 To targ2
icolour = 3
Case targ3 To targ4
icolour = 45
Case targ5 To targ6
icolour = 45
Case targ7 To targ8
icolour = 3
Case Else
ActiveCell.Interior.ColorIndex = xlColorIndexNone
End Select
Target.Interior.ColorIndex = icolour
End If

Error:
'Error code still to be written to handle run time error 13 when block
deleting cells contents.

End Sub

 

[Tom Ogilvy]

The code worked fine for me. Suspect the trouble is you values are not
contiguous, so some numbers are left out, our your names are not defined
properly. Anyway, it doesn't appear to be in your code. (note that this
won't work like conditional formatting - it won't fire just on a
recalculation - only when a cell is edited manually, by DDE or by code.