I am writing a function (boolean) that tests if a cell is a direct
precedent of another cell. Any ideas? I am leaning towards using the
intersect of cell and direct precedents range null test as my
indicator.

Jeremy

Sounds good, but there is a problem. It doesn't work.

DirectPrecedents seems to work differently in a User Defined Function.
Presumably, this is a function of the calculation
engine.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jdcox1999" wrote in message
ups.com...
I am writing a function (boolean) that tests if a cell is a direct
precedent of another cell. Any ideas? I am leaning towards using the
intersect of cell and direct precedents range null test as my
indicator.

Jeremy

I get it to work from another sub but not from a worksheet cell (see
below). Any suggestions?


Sub testfunction()
Dim i As Boolean
i = IsDirectPrecedentOf(Range("BN45"), Range("BP45"))
MsgBox (i)
End Sub


Public Function IsDirectPrecedentOf(ByRef SubjectCell As Range, ByRef
ObjectCell As Range) As Boolean
IsDirectPrecedentOf = False
If Not Intersect(SubjectCell, ObjectCell.DirectPrecedents) Is Nothing
Then
IsDirectPrecedentOf = True
Else
IsDirectPrecedentOf = False
End If
End Function


Bob Phillips wrote:
Sounds good, but there is a problem. It doesn't work.

DirectPrecedents seems to work differently in a User Defined Function.
Presumably, this is a function of the calculation
engine.

--
HTH

Bob Phillips

It won't work from a worksheet as I explained, and I am afraid I don't
have a solution.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jdcox1999" wrote in message
oups.com...
I get it to work from another sub but not from a worksheet cell (see
below). Any suggestions?


Sub testfunction()
Dim i As Boolean
i = IsDirectPrecedentOf(Range("BN45"), Range("BP45"))
MsgBox (i)
End Sub


Public Function IsDirectPrecedentOf(ByRef SubjectCell As Range, ByRef
ObjectCell As Range) As Boolean
IsDirectPrecedentOf = False
If Not Intersect(SubjectCell, ObjectCell.DirectPrecedents) Is Nothing
Then
IsDirectPrecedentOf = True
Else
IsDirectPrecedentOf = False
End If
End Function


Bob Phillips wrote:
Sounds good, but there is a problem. It doesn't work.

DirectPrecedents seems to work differently in a User Defined Function.
Presumably, this is a function of the calculation
engine.

--
HTH

Bob Phillips

We went with a text search on the string version of the range. Since
it will be limited in application this is ok. Anyways we use this
formula within conditional formatting. ie. we want to bold a cell if
it is a precedent of another. Using worksheet change routine works,
but we want to use ctrl+z. the string search works but is taxing on
the virtual memory when done in conditional formatting.



Function IsDirectPrecedentOf(subjectcell As Range, objectcell As Range)
As Boolean
IsDirectPrecedentOf = False
IsDirectPrecedentOf = InStr(1, objectcell.Formula,
subjectcell.Address(0, 0))
End Function

Bob Phillips wrote:
It won't work from a worksheet as I explained, and I am afraid I don't
have a solution.

--
HTH

Bob Phillips