Shortcut to know Named Range of a Cell in Excel 2010

Option Explicit

Sub test()
MsgBox GetNamedRange(Workbooks("YourWorkbookName.xls").Worksheets("YourWorksheetName").Range("A2"))
MsgBox GetNamedRange(ActiveCell)
End Sub

'returns the name of NamedRange
Function GetNamedRange(rng As Range) As String
Dim r As Range, n As Name
Dim wsh As Worksheet, wbk As Workbook
Dim sRetVal As String

On Error Resume Next

Set wsh = rng.Parent
Set wbk = wsh.Parent

'go through the collection of names, including the all worksheet-specific names
For Each n In wbk.Names
    r = n.RefersTo
    If r.Address = rng.CurrentRegion.Address Then
        sRetVal = n.NameLocal
        Exit For
    End If
Next

GetNamedRange = sRetVal

End Function