Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code works intermittantly is there a fault?
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")).V alue 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code works intermittantly is there a fault?
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. -- Regards, Tom Ogilvy "Rob Hargreaves" wrote in message ... 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")).V alue 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
code works - need to save | Excel Discussion (Misc queries) | |||
Code works within VBE but not from Excel | Excel Programming | |||
Code works but I have to run it 4 times | Excel Programming | |||
Why won't this code works | Excel Programming | |||
VBA Code works in 2000 not 97 | Excel Programming |