Solved: Find named range that activecell is in

[Kippers]

I have 9 named ranges that do not overlap
i.e.
Named range
If i select a cell how can i get the named range that it is in?

[acw]

Hi

This bit of code may help.

[VBA]
Sub aaa()
For i = 1 To Names.Count
If Not Intersect(ActiveCell, Range(Names(i))) Is Nothing Then
MsgBox Names(i).Name
End If
Next i
End Sub
[/VBA]

Tony

[Bob Phillips]

You might need some error handling

[vba]

Public Function CellsName(rng As Range) As String
Dim sh As Worksheet
Dim refRange As Range
Dim nme As Name
CellsName = ""
On Error GoTo ErrHandler
For Each nme In ThisWorkbook.Names
Set refRange = Nothing
On Error Resume Next
Set refRange = nme.RefersToRange
On Error GoTo ErrHandler
If Not refRange Is Nothing Then
If rng.Parent.Name = refRange.Parent.Name Then
If Not Intersect(rng, refRange) Is Nothing Then
CellsName = nme.Name
Exit Function
End If
End If
End If
Next
Exit Function
ErrHandler:
CellsName = "*** error ***"
End Function
[/vba]

[Kippers]

Excellent these solve the problem - thanks