Worksheet Change Event Using Named Ranges

[FracinDean]

Can I use named ranges to trigger actions using the Worksheet Change event? I'm having trouble discovering the correct syntax. I tried Target.Range and Target.Name and Target.Address but those didn't work for me. I have several different cells & actions, so something with a SELECT / CASE structure could be really slick. Any ideas?

 

[vconfused]

Maybe something like:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim nam As Name

For Each nam In ActiveWorkbook.Names
If Not Application.Intersect(Target, Range(nam.RefersTo)) Is Nothing Then
'run code
MsgBox "Would you like to play a game?"
Else
'no hit
End If
Next
End Sub


I've never done this so I'm probably missing things like: what happens if the RefersTo is not a range.

 

[FracinDean]

Thanks vconfused. The "Not Application.Intersect Is Nothing" worked for me, I wasn't familiar with that method. Since I didn't want to execute code on ALL the named ranges and I wanted to run different code for different ranges, a slight variation of what you suggested got it done for me:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("NamedRange1")) Is Nothing Then

'code block 1
​

ElseIf Not Application.Intersect(Target, Range("NamedRangeN")) Is Nothing Then

'code block N
​

End If
End Sub