Something like this perhaps... The range object does not have to be premised
with the sheet it comes from as that is inherant in the range object
itself... Use the formula like this =computecount(Sheet2!A1)

Public Function ComputeCount(ByVal Cell As Range) As Integer
If Cell.Interior.Color = vbRed Then
'* do the math one way
ComputeCount = 1
Else
'* do the math another way
ComputeCount = 2
End If
End Function

It is important to note that changing the colour of a cell does not trigger
a calculation, nor does it dirty the cell to force a recalculation of the
cells pointing at it... You can add application.volatile to recalc the cell
but you will still have to force a calc when the colour is changed. This will
mean that there will be a fair bit of overhead to this function. Just
something to be aware of...
--
HTH...

Jim Thomlinson


"Mark VII" wrote:

Hello --

I need to create a custom function that I will use in a formula, and it
needs to take action based on the contents of another cell on another
worksheet in the same workbook. The idea is to get a different from the
formula based on the color of the cell on the other worksheet. Sort of like
this:

Public Function ComputeCount (<cell reference) as integer

if <cell reference.Interior.Color = vbRed then
'* do the math one way
else
'* do the math another way
end if

end Function

While I'm pretty proficient at VBA, I'm just not seeing how to get the
function pointing to the cell of interest on the worksheet of interest. I'm
used to setting an Worksheet object variable to reference a given worksheet
and a Range object variable to reference a given cell (such as in
batch-oriented processing), but I've never tried this in a function to be
used in a formula.

Any suggestions? Thanks in advance,

Mark