Detect if a selected cell is within a named range

[CraigB]

If I have a worksheet with a number of different (non-overlapping)
custom named ares (i.e. part of the WorkSheet.Names collection), is
there any simple way I can detect the region that a selected cell
might be part of?

I could get the row and column index of the cell in question and then
loop through all the Name objects in the collection, testing if it
falls within the R and C range, but I was hoping there might be
something simpler.

Thanks in advance

Craig

 

[Bob Phillips]

Dim nme As Name

For Each nme In ActiveWorkbook.Names
If Not Intersect(ActiveCell, Range(nme.Name)) Is Nothing Then
MsgBox nme.Name
End If
On Error GoTo 0
Next nme


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[JE McGimpsey]

Note that this will work, but only if

a) all the Names in the workbook refer to ranges, and
b) all the named ranges are on the active sheet.

One workaround:

Dim nmTest As Name
Dim rTest As Range

On Error Resume Next
For Each nmTest In ActiveWorkbook.Names
Set rTest = Intersect(ActiveCell, nmTest.RefersToRange)
If Not rTest Is Nothing Then
MsgBox nmTest.Name
End If
Next nmTest
On Error GoTo 0