I know the Worksheet_Change fires for when a user updates a range of
cells and the range of cells is passed to the Worksheet_Change sub.
What about when a cell is changed from a formula? Worksheet_Change
doesn't seem to fire when a cell is changed as a result of a forumula
calucation.

Example:
Cell A2 is calcuated by the value of A1 + 1

If you change A1, A2 now changes as well but the Worksheet_Change is
fired but the Target only includes A1 and Worksheet_Change is not
fired again for A2.

Is there a way to capture the change just on A2?

Obviously on every change i can just check to see if i need to make my
adjustments. But their is a performance reason why i cannot. So i only
want to run if indeed the correct cell has been changed not on every
change.

You may need to use the Calculate Event instead of the Change Event.
--
Gary''s Student - gsnu2007g


" wrote:

I know the Worksheet_Change fires for when a user updates a range of
cells and the range of cells is passed to the Worksheet_Change sub.
What about when a cell is changed from a formula? Worksheet_Change
doesn't seem to fire when a cell is changed as a result of a forumula
calucation.

Example:
Cell A2 is calcuated by the value of A1 + 1

If you change A1, A2 now changes as well but the Worksheet_Change is
fired but the Target only includes A1 and Worksheet_Change is not
fired again for A2.

Is there a way to capture the change just on A2?

Obviously on every change i can just check to see if i need to make my
adjustments. But their is a performance reason why i cannot. So i only
want to run if indeed the correct cell has been changed not on every
change.

From a previous post:

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

Regards,
Peter T

wrote in message
...
I know the Worksheet_Change fires for when a user updates a range of
cells and the range of cells is passed to the Worksheet_Change sub.
What about when a cell is changed from a formula? Worksheet_Change
doesn't seem to fire when a cell is changed as a result of a forumula
calucation.

Example:
Cell A2 is calcuated by the value of A1 + 1

If you change A1, A2 now changes as well but the Worksheet_Change is
fired but the Target only includes A1 and Worksheet_Change is not
fired again for A2.

Is there a way to capture the change just on A2?

Obviously on every change i can just check to see if i need to make my
adjustments. But their is a performance reason why i cannot. So i only
want to run if indeed the correct cell has been changed not on every
change.

So long as the dependants of the cell with the formula are on the same sheet
as the formula then you can catch changes to those dependant cells like
this...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("A2"), Target.Dependents) Is Nothing Then
MsgBox "Tada"
End If
End Sub
--
HTH...

Jim Thomlinson


" wrote:

I know the Worksheet_Change fires for when a user updates a range of
cells and the range of cells is passed to the Worksheet_Change sub.
What about when a cell is changed from a formula? Worksheet_Change
doesn't seem to fire when a cell is changed as a result of a forumula
calucation.

Example:
Cell A2 is calcuated by the value of A1 + 1

If you change A1, A2 now changes as well but the Worksheet_Change is
fired but the Target only includes A1 and Worksheet_Change is not
fired again for A2.

Is there a way to capture the change just on A2?

Obviously on every change i can just check to see if i need to make my
adjustments. But their is a performance reason why i cannot. So i only
want to run if indeed the correct cell has been changed not on every
change.