如果所选内容与命名区域相交，则选择该命名区域

Question

我的第一个计划是为每个命名范围重复此操作，直到我意识到这将是多少。

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

Answer 1

首先，您需要将该activecell更改为target target因为作为调用范围是静态的。此外，只需向IF添加NOT条件，这样您就不必使用该ELSE

如果要根据命名区域列表测试target以查看target是否与至少一个命名区域相交，可以使用应用程序的 union 方法：

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

如果需要更多控制，也可以执行循环：

 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

Answer 2

添加一个循环来循环访问命名范围：

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