Just to add:


to the OP
Since you indicated the dependent area was in a separate sheet, you might be
a bit more discerning:

Private Sub Workbook_SheetSelectionChange( _
ByVal Sh As Object, ByVal Target As Range)
if Sh.Name = "Colors" then
worksheets("UDFs").Calculate
end if
End Sub

the disadvantage is that this assumes the user will make a selection on that
sheet after changing the color of the subject cell. To account for the
fact that that might not happen, you might want to also put in the
worksheets("UDFs").Calculate command in the sheet deactive event for the
"colors" sheet.

--
Regards,
Tom Ogilvy


"Jim Thomlinson" wrote:

You can add event code that will reclac on selection change. There is no
event generated when a cell is coloured so selection change is about as close
as you will get. This will force a recalc of all dirty cells each time the
cursor is moved. By adding application.volatile to the original code you get
very close to what you were looking for...

'**In a standard code module
Public Function ComputeCount(ByVal Cell As Range) As Integer
Application.Volatile
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

'** In the Thisworkbook module
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
Sh.Calculate
End Sub
--
HTH...

Jim Thomlinson


"Mark VII" wrote:

Jim --

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...<

That's an interesting point. Is there a way to detect that the cursor has
left a particular cell, or is there a way to capture the cell that was last
edited? Under my set of somewhat arcane circumstances, I can work around the
lack of automatic recalculation when cell color changes if I can monitor a
given area of the worksheet for edits. My thought is that if I can detect
that a particular cell was edited, I can force a calculation in the dependent
area of my workbook.

Thanks,
Mark