R
Robert Hargreaves
Hi everyone I'm a bit puzzled over where to put some code to make it work.
I have got a public sub in my excel file. I know how you should call this
in code but I do not know if this is the correct place for it.
I only want the contents of the code to work on one sheet and I only would
like the code to apply to columns Y, Z, AA, AB
I dont know what the declaration name should be like onactivate or onchange.
can someone help please. Patrick Malloy, I couldn't find your code you
recommeded before.
Thankyou for your help.
Rob
Here is a copy of the code in the sub
Public Sub ConditionalFill()
Dim val As Long
Dim nr1 As Long
Dim nr2 As Long
Dim nr3 As Long
Dim nr4 As Long
Dim nr5 As Long
Dim nr6 As Long
Dim nr7 As Long
Dim nr8 As Long
val = ActiveCell.Value
nr1 = Range(ActiveWorkbook.Names("NaburnMLSSTrig1a")).Value
nr2 = Range(ActiveWorkbook.Names("NaburnMLSSTrig1b")).Value
nr3 = Range(ActiveWorkbook.Names("NaburnMLSSTrig2a")).Value
nr4 = Range(ActiveWorkbook.Names("NaburnMLSSTrig2b")).Value
nr5 = Range(ActiveWorkbook.Names("NaburnMLSSTrig3a")).Value
nr6 = Range(ActiveWorkbook.Names("NaburnMLSSTrig3b")).Value
nr7 = Range(ActiveWorkbook.Names("NaburnMLSSTrig4a")).Value
nr8 = Range(ActiveWorkbook.Names("NaburnMLSSTrig4b")).Value
Select Case True
Case val > nr1 And val < nr2
ActiveCell.Interior.ColorIndex = 45
Case val > nr3 And val < nr4
ActiveCell.Interior.ColorIndex = 3
Case val > nr5 And val < nr6
ActiveCell.Interior.ColorIndex = 45
Case val > nr7 And val < nr8
ActiveCell.Interior.ColorIndex = 3
Case Else
MsgBox ("Non Apply"), vbInformation
ActiveCell.Interior.ColorIndex = xlColorIndexNone
End Select
End Sub
I have got a public sub in my excel file. I know how you should call this
in code but I do not know if this is the correct place for it.
I only want the contents of the code to work on one sheet and I only would
like the code to apply to columns Y, Z, AA, AB
I dont know what the declaration name should be like onactivate or onchange.
can someone help please. Patrick Malloy, I couldn't find your code you
recommeded before.
Thankyou for your help.
Rob
Here is a copy of the code in the sub
Public Sub ConditionalFill()
Dim val As Long
Dim nr1 As Long
Dim nr2 As Long
Dim nr3 As Long
Dim nr4 As Long
Dim nr5 As Long
Dim nr6 As Long
Dim nr7 As Long
Dim nr8 As Long
val = ActiveCell.Value
nr1 = Range(ActiveWorkbook.Names("NaburnMLSSTrig1a")).Value
nr2 = Range(ActiveWorkbook.Names("NaburnMLSSTrig1b")).Value
nr3 = Range(ActiveWorkbook.Names("NaburnMLSSTrig2a")).Value
nr4 = Range(ActiveWorkbook.Names("NaburnMLSSTrig2b")).Value
nr5 = Range(ActiveWorkbook.Names("NaburnMLSSTrig3a")).Value
nr6 = Range(ActiveWorkbook.Names("NaburnMLSSTrig3b")).Value
nr7 = Range(ActiveWorkbook.Names("NaburnMLSSTrig4a")).Value
nr8 = Range(ActiveWorkbook.Names("NaburnMLSSTrig4b")).Value
Select Case True
Case val > nr1 And val < nr2
ActiveCell.Interior.ColorIndex = 45
Case val > nr3 And val < nr4
ActiveCell.Interior.ColorIndex = 3
Case val > nr5 And val < nr6
ActiveCell.Interior.ColorIndex = 45
Case val > nr7 And val < nr8
ActiveCell.Interior.ColorIndex = 3
Case Else
MsgBox ("Non Apply"), vbInformation
ActiveCell.Interior.ColorIndex = xlColorIndexNone
End Select
End Sub