Mega Code Archive

 

Categories / VisualBasic Script / Excel

 

Find out which names are overlapping the selected cells, regardless of whether they entirely contain the selected cells

Sub NamesOverlappingSelection()          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 = range(nmName.name)              If Not rngNameRange Is Nothing Then                  If rngNameRange.Parent.name = ActiveSheet.name Then                     Set rng = Intersect(Selection, rngNameRange)                     If Not rng Is Nothing Then                             sMessage = sMessage & nmName.name & vbCr                     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