Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Germany, Germany
    Posts
    169
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cell element of Range? (XP SP3 / General)

    Hello Everybody,

    I try to find an easy way to identify whether a selected cell is a member of a named range. The range behind the name changes dynamically according to some conditions in the worksheet, and I need to identify whether a selected cell is a member of this range or not (in order to perform some specific action).
    Is there a sheet function that could do something like this. I know it could be done with some VBA code; I am just wondering if there is a predefined function for this.

    Best regards,

    Porley

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell element of Range? (XP SP3 / General)

    I think you need VBA. You could create a user-defined function:

    Function CellInRange(oCell As Range, oRange As Range) As Boolean
    CellInRange = Not Intersect(oCell, oRange) Is Nothing
    End Function

    And use it like this in a worksheet formula:

    =CellInRange(D3,TestRange)

    If you put the function in your Personal.xls, use

    =Personal.xls!CellInRange(D3,TestRange)

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell element of Range? (XP SP3 / General)

    I don't know of a way to do that with builtin functions. The following UDF will return True if any cells in the first argument are also in the second argument:

    <code>
    Public Function IsCellInRange(oCell As Range, oRng As Range) As Boolean
    IsCellInRange = Not Intersect(oCell, oRng) Is Nothing
    End Function
    </code>
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Germany, Germany
    Posts
    169
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell element of Range? (XP SP3 / General)

    Hello to you both,

    thank you very much for your reply. I will use your solution, it sounds very good, though I cannot use it in a workbook directly. The way I thought of was much more complicated. So thanks again!!

    Best regards, Porley

Posting Permissions

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