Just to add if Brad is new to the topic - Worksheet event code goes in the
relevant sheet module. Rt-click the sheet tab - View code takes you directly
into it.

Look at the top-mid drop down and select worksheet, then select the desired
event from the top right dropdown.

Although normally it's best to qualify a range to a particular sheet, in the
case of code in the sheet's object module it's best not to, unless the
intention is to refer to a range on another sheet..
Range("A1") in a worksheet module will always refer to it's own sheet, not
necessarily the activesheet or even a sheet in activeworkbook.

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.

You need to trap the change of one or more constant cells directly or
indirectly referenced by the formula.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
On Error GoTo errExit
Application.EnableEvents = False
Set rng = Range("C1").Precedents

If Not Intersect(Target, rng) Is Nothing Then

If Range("C1").Value < 0 Then
' do something
MsgBox Range("C1").Value
End If
End If

errExit:
Application.EnableEvents = True
End Sub

The above is looking for any change in cell(s) linked to C1. Big caveat,
unfortuantely rng.Precedents does not return cells on other sheets, more
work to do if necessary

It might be worth experimenting with similar code in the sheet's Calculate
event (not code in each of course)

Regards,
Peter T


"Mike H" wrote in message
...
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