The function will live in an Add-In of general-purpose tools that are
available to the entire site, and so should be self-contained. Even my own
use will be primarily for reviewing workbooks that I don't control and
therefore should not modify. Nor can I assume that the sheet of interest has
ever been active in the current Excel session.

Jerry

"Peter T" wrote:

Without knowing your overall objective a couple of approaches you might
consider, to return the activecell on the non active sheet in a UDF

1. In Workbook_SheetSelectionChange Add the activecell to a public
collection, say gColCells, with key sheetname, after deleting the previous
key/item.

Initialize the collection in the wb activate by looping worksheets and
adding each active cell. Plenty of error handling in case removing non
existent key, activechart etc. Recalc in sheet activate or deactivate.

Pass SheetName!cell-ref to the UDF and 'gColCells(cel.Parent.Name)' should
refer to the activecell object on SheetName.

2 In cell A1 in sheets you are interested in, ie your CF sheets, add a CF
with formula =A1, no format required.

Pass SheetName!cell-ref to the UDF and
sF= cel.Parent.Range("a1").FormatConditions(1).Formula 1
sAddr = Mid(sF, 2, 10)

sAddr should refer to the address of the activecell on SheetName

Recalc probably required, when & where depending on overall scenario

The CF could be in any cell, not necessarily A1, with the formula referring
to itself, adjust the UDF code to return from same.

Regards,
Peter T

"Jerry W. Lewis" wrote in message
...
Thank you for joining in. Fortunately, you correct observation does not
impact my objective.

Jerry

"Peter T" wrote:

Hi Jerry,

If, and I'm guessing, your objective is to evaluate a CF formula that
contains relative address(es), even if you could get the activecell on
the
non-active sheet wouldn't you still have a problem. Wouldn't Evaluate
then
use addresses from the active sheet.

Regards,
Peter T

"Jerry W. Lewis" wrote in message
...
Without activating the sheet, how can I determine which cell on that
sheet
that will become the ActiveCell once the sheet is activated?

I am trying to write a UDF that will be called from Excel (therefore
changing selections is not an option) that will return .Formula1 from
the
conditional format of a cell whose reference is passed to the
function.
The
problem is that all relative addressing in .Formula1 is automatically
converted to A1 style relative to the cell that would be ActiveCell if
the
cell's .parent were ActiveSheet. John Walkenbach's suggestion
http://j-walk.com/ss/excel/odd/odd07.htm
only works on ActiveSheet, but could easily be modified if I could
identify
the equivalent of ActiveCell for a sheet that is not ActiveSheet.

Jerrry