Declaration name

[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

 

[Guest]

The answer depends on when you want this to occur (what event should trigger
it?). It appears you are using the activecell value to trigger the format
changes, so i am assuming you want this to occur after the user changes the
value in the active cell. That would be the Worksheet_Change event, so the
Sub declaration would be:

Public Sub Worksheet_Change(ByVal Target as Range)

You would need to put the code in the proper code module for that worksheet
(so select the sheet from the list in the Project Explorer inside the VBA
editor before entering it).

Target will be the active cell, so to have it run only for the columns you
mentioned you can put it inside an IF statement (Intersect checks the overlap
of the two ranges - if there is no overlap it is Nothing):

Public Sub Worksheet_Change(ByVal Target as Range)
' Dim statements...
If Not(Intersect(Target, Range("Y:AB")) Is Nothing) Then
' Your code here
End If
End Sub

 

[JE McGimpsey]

Try putting this in your worksheet code module (right-click on the
worksheet tab and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
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

With Target(1)
If Not Intersect(.Cells, Range("Y:AB")) Is Nothing Then
With .Parent.Parent 'ActiveWorkbook
nr1 = Range(.Names("NaburnMLSSTrig1a")).Value
nr2 = Range(.Names("NaburnMLSSTrig1b")).Value
nr3 = Range(.Names("NaburnMLSSTrig2a")).Value
nr4 = Range(.Names("NaburnMLSSTrig2b")).Value
nr5 = Range(.Names("NaburnMLSSTrig3a")).Value
nr6 = Range(.Names("NaburnMLSSTrig3b")).Value
nr7 = Range(.Names("NaburnMLSSTrig4a")).Value
nr8 = Range(.Names("NaburnMLSSTrig4b")).Value
End With

val = .Value
Select Case True
Case val > nr1 And val < nr2
.Interior.ColorIndex = 45
Case val > nr3 And val < nr4
.Interior.ColorIndex = 3
Case val > nr5 And val < nr6
.Interior.ColorIndex = 45
Case val > nr7 And val < nr8
.Interior.ColorIndex = 3
Case Else
MsgBox "Non Apply", vbInformation
.Interior.ColorIndex = xlColorIndexNone
End Select
End If
End With
End Sub

 

[William Benson]

Out of curiosity, if it is the current cell that needs to be evaluated,
could you hold a static variable in the Workbook_SheetSelectionChange event,
set it equal to the target with every exit of the function, then test the
static variable every time you enter the sub?

 

[JE McGimpsey]

It's not quite that easy.

Workbook_SheetSelectionChange returns the Selection, not the ActiveCell
(though if only one cell is selected, they're the same).

For instance, if I select A1:J10, then I can enter 100 values (say,
entering a value and hitting Enter or Tab), changing the ActiveCell each
time, without ever firing Workbook_SheetSelectionChange.