+ Reply to Thread
Results 1 to 3 of 3

Conditional Formatting to HIghlight named ranges - error

  1. #1
    Malte Nuhn
    Guest

    Conditional Formatting to HIghlight named ranges - error


    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


  2. #2
    Tom Ogilvy
    Guest

    Re: Conditional Formatting to HIghlight named ranges - error

    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

    --
    Regards,
    Tom Ogilvy



    "Malte Nuhn" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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
    >




  3. #3
    Malte Nuhn
    Guest

    Re: Conditional Formatting to HIghlight named ranges - error

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



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1