Function InNamedRanges(Optional inCell As Range) As String

Dim myName As Name
Dim myAddress As String
Dim myMessage As String
Dim myRange As Range
Dim inRange As Integer

If inCell Is Nothing Then Set inCell = Application.Caller
Hi Folks,

trying to get Excel 2003 to highlight all named ranges in a sheet.
Ranges are defined self-adjusting (using offset... count), and I want
users to be able to see at any given point what area they cover.

My approach was to use conditional formatting, and putting in a
VBA-created formula there that would output TRUE if cell was in a named
range, and FALSE if not.

I've had a look at a number of entries in this and other groups, but
none of the examples they give seem to work. Here is my function:

Function isInNamedRange(inCell As Range)

For Each myName In ActiveWorkbook.Names
Debug.Print myName.Name; myName.RefersTo
myAddress = myName.RefersTo
If Not Application.Intersect(inCell, Range(myAddress)) Is Nothing
Then isInNamedRange = True: Exit Function
Next myName
isInNamedRange = False

End Function


Excel gives a #VALUE message if I use this in a sheet, and the
debug.print statement only prints out the line it gets to before the
intersect bit. I am clueless - any help?

Thanks in advance!
Malte