Using a named range to update if/or in VBA
Posted by sid on October 05, 2001 2:16 PM
I am using the code below on numerous sheets. i want to be able to update all sheets by just changing a few cells in a named range, replacing all the or statements with the range, but I can't find a way to get it to work - any ides anyone - thanx
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = "ACo" Or Target.Value = "ACv" Or Target.Value = "ACw" Or Target.Value = "AF" Or Target.Value = "AT" Or Target.Value = "BT" Or Target.Value = "DM" Or Target.Value = "JM" Or Target.Value = "ML" Or Target.Value = "NO" Or Target.Value = "PT" Or Target.Value = "SB" Or Target.Value = "SS" Or Target.Value = "TJ" Then
Target.Offset(0, 1).Value = Date
End If
Posted by Henry Root on October 05, 2001 3:34 PM
You don't need to use a named range.
If you put the values to be checked in Sheet1 starting at A1, then :-
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns(2)) Is Nothing Then
Dim rng As Range
Set rng = Worksheets("Sheet1").Columns(1).Find(Target.Value)
If Not rng Is Nothing Then Target.Offset(0, 1).Value = Date
End If
End Sub
You can add and delete to the list in Sheet1 Column A without having to change the macro.
Note : the first line restricts the worksheet_change procedure to changes input to column B only - revise to suit your requirements.
Posted by sid on October 05, 2001 4:17 PM
Thanx Henry, thats neat. OK in 2000 but will need to check in 97, the trigger cells are data validated which 97 has difficulty seeing as worksheetchanges.
1 more question - whats the syntax for for targeting 2 separated columns?
cheers sid
Posted by Henry Root on October 05, 2001 8:06 PM
If Not Intersect(Target, Range(Columns(2), Columns(4))) Is Nothing Then
