MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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