I am having trouble getting a macro to "fire" when a cell change is made.
The cell is a formula, and the formula is based on a cell that "refreshes"
every 30 minutes from a web query. When the cell hits a certain value it
should send an email to several workers, but this does not occur.

Any thoughts/suggestions?

here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Cells.Count 1 Then Exit Sub
On Error GoTo EndMacro
If Not Target.HasFormula Then
Set rng = Target.Dependents
If Not Intersect(Range("C11"), rng) Is Nothing Then
If Range("C11").Value < 0 Then MYMACRO
End If
End If
EndMacro:
End Sub

From what I'm finding, the dependents are the cells that reference the target
cell. Is that what you want? No matter, I think I'd change it as follows

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Cells.Count 1 Then Exit Sub
'On Error GoTo EndMacro
If Not Target.HasFormula Then
On Error Resume Next
Set rng = Target.Dependents
On Error GoTo 0
If Not rng Is Nothing Then
If Not Intersect(Range("C11"), rng) Is Nothing Then
If Range("C11").Value < 0 Then
MYMACRO
End If
End If
Else
Debug.Print "There are no dependents to cell " & Target.Address
End If
EndMacro:
End Sub

HTH,
Barb Reinhardt


"Macro not running as intended" wrote:

I am having trouble getting a macro to "fire" when a cell change is made.
The cell is a formula, and the formula is based on a cell that "refreshes"
every 30 minutes from a web query. When the cell hits a certain value it
should send an email to several workers, but this does not occur.

Any thoughts/suggestions?

here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Cells.Count 1 Then Exit Sub
On Error GoTo EndMacro
If Not Target.HasFormula Then
Set rng = Target.Dependents
If Not Intersect(Range("C11"), rng) Is Nothing Then
If Range("C11").Value < 0 Then MYMACRO
End If
End If
EndMacro:
End Sub

Worksheet_Change event is not triggered by a calculated value change.

Use Worksheet_Calculate event


Gord Dibben MS Excel MVP


On Thu, 16 Oct 2008 09:52:01 -0700, Macro not running as intended <Macro not
running as wrote:

I am having trouble getting a macro to "fire" when a cell change is made.
The cell is a formula, and the formula is based on a cell that "refreshes"
every 30 minutes from a web query. When the cell hits a certain value it
should send an email to several workers, but this does not occur.

Any thoughts/suggestions?

here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Cells.Count 1 Then Exit Sub
On Error GoTo EndMacro
If Not Target.HasFormula Then
Set rng = Target.Dependents
If Not Intersect(Range("C11"), rng) Is Nothing Then
If Range("C11").Value < 0 Then MYMACRO
End If
End If
EndMacro:
End Sub

Thanks for the help so far guys.

I received an error notice when I changed to Calculate...do I need to
specify the event? How do I do that?

"Gord Dibben" wrote:

Worksheet_Change event is not triggered by a calculated value change.

Use Worksheet_Calculate event


Gord Dibben MS Excel MVP


On Thu, 16 Oct 2008 09:52:01 -0700, Macro not running as intended <Macro not
running as wrote:

I am having trouble getting a macro to "fire" when a cell change is made.
The cell is a formula, and the formula is based on a cell that "refreshes"
every 30 minutes from a web query. When the cell hits a certain value it
should send an email to several workers, but this does not occur.

Any thoughts/suggestions?

here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Cells.Count 1 Then Exit Sub
On Error GoTo EndMacro
If Not Target.HasFormula Then
Set rng = Target.Dependents
If Not Intersect(Range("C11"), rng) Is Nothing Then
If Range("C11").Value < 0 Then MYMACRO
End If
End If
EndMacro:
End Sub