Unfortunately the cell I'm interested in is changed by the summation of dozens
of other cells (i.e. a formula is used). And those cells in turn are changed
by other formulas. And since Worksheet_Change doesn't work on cells with
formulas, it's very difficult to figure out how to use Worksheet_Change to
figure out if the cell I'm interested is currently a zero or not.

Seems like Excel should provide a way to sense if a cell has changed,
regardless if that cell was modified manuall or via a formula. If I could
sense that my cell has changed then I could see if it's a zero or not and
execute code accordingly. Worksheet_Change would be perfect if it worked for
cells that change via formulas.

Thanks,
Brad



Mike H wrote:

Brad,

You can still use the worksheet change event but you must use the cell that
changes your cell to zero. For example this on worksheet2 uses the change
event for cell A1 but then goes on to look at Sheet 1 a1

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If Sheets("Sheet1").Range("A1").Value = 0 Then
MsgBox "A1 on sheet 1 just changed to " &
Sheets("Sheet1").Range("A1").Value
' do something
End If
End If
End Sub

Mike

"Brad Stone" wrote:

Hi all,

I'm fairly new to VBA programming. I'm using Excel 2003. I need to
know when a particular cell (it's actually a single cell "range" called
MYRANGE) has changed. When I detect the change, I need to see if the
new numeric value in that cell is not zero; if it's non-zero then I need
to activate some code. Unfortunately that cell is changed via a
formula - so using Worksheet_Change is apparently not going to work.
That is, Worksheet_Change doesn't get triggered for cells of this type.

I tried using .OnTimer to repeatedly check the value of that cell, but
I couldn't get it to work very well.

It seems like there has got to be an easy way to trigger some code to
activate when a cell has changed (even for a cell that changes via a
formula). Thanks for any help!

-Brad