CODEWITHSUNDEEP
excelvba
mrkrister
mrkrister

Reputation: 67

If selection intersects with a named range then select that named range

My first plan was to repeat this for every named range, until i realized how much that would be.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(ActiveCell, Range("M_1")) Is Nothing Then
Else
    Range("M_1").Select
End If

End Sub

Upvotes: 0

Views: 3378

Answers (2)

Scott Craner
Scott Craner

Reputation: 152585

Add a loop to iterate through the named ranges:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim nm As Name
Dim nmStr As String
For Each nm In ThisWorkbook.Names
    nmStr = nm.Name
    If Not Intersect(Target, Range(nmStr)) Is Nothing Then
        Application.EnableEvents = False
        Range(nmStr).Select
        Application.EnableEvents = True
    End If
Next nm

End Sub

Upvotes: 1

JNevill
JNevill

Reputation: 50209

First you'll want to change that activecell to target since target is static as the calling range. Also, just add a NOT condition to your IF so you don't have to use the ELSE

If you wanting to test target against a list of named ranges to see if target intersects at least one of the named ranges, you can use the application's union method:

If Not Intersect(Target, Union(Range("M_1"), Range("M_2"), Range("M_3")) Is Nothing Then

You could also do a loop if you need more control:

 doIntersect = false
 rngCounter = 0
 For each strTestRange in Array("M_1", "M_2", "M_3")
     If Not Intersect(Target, Range(strTestRange) Is Nothing Then
         doIntersect = true
         rngCounter = rngCounter + 1
     End if
 Next strTestRange

 If doIntersect Then
      msgbox(rngCounter & " named ranges intersect your selection")
 Else
      msgbox("None of the named ranges intersected your selection")
 End if

Upvotes: 2

Related Questions