Mega Code Archive

 

Categories / VisualBasic Script / Excel

 

Determining which Names Overlap a Range

Sub SelectionEntirelyInNames()          Dim sMessage As String          Dim nmName As name          Dim rngNameRange As range          Dim rng As range          On Error Resume Next          For Each nmName In Names              Set rngNameRange = Nothing              Set rngNameRange = nmName.RefersToRange              If Not rngNameRange Is Nothing Then                  If rngNameRange.Parent.name = ActiveSheet.name Then                     Set rng = Intersect(Selection, rngNameRange)                     If Not rng Is Nothing Then                         If Selection.Address = rng.Address Then                             sMessage = sMessage & nmName.name & vbCr                         End If                     End If                  End If              End If          Next nmName          If sMessage = "" Then              MsgBox "The selection is not entirely in any name"          Else              MsgBox sMessage          End If      End Sub