Conditional Formatting to HIghlight named ranges - error

[Malte Nuhn]

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

 

[Tom Ogilvy]

Function isInNamedRange(inCell As Range)
Dim myName as Name, rng as Range
Dim myAddress as String
For Each myName In ActiveWorkbook.Names
Debug.Print myName.Name; myName.RefersTo
myAddress = myName.RefersTo
On Error Resume Next
set rng = myName.RefersToRange
On Error goto 0
if not rng is nothing then
if inCell.Parent.Name = rng.Parent.Name then
If Not Application.Intersect(inCell, rng) Is Nothing Then _
isInNamedRange = True: Exit Function
end if
end if
Next myName
isInNamedRange = False
End Function

 

[Malte Nuhn]

Thank you Tom - this does the job! I would never have come up with the