triggering a macro when a formula result changes

[mrclox]

Hi, can anyone tell me if its possible and if so how i can trigger a macro automatically when a formula result changes a cell? I can get it to trigger when a formula result changes using this...

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("A1"), rng) Is Nothing Then
If Range("A1").Value > 200 Then YourMacroName
End If
End If
EndMacro:
End Sub

but it requires human mouse strokes to fire. I want it to do this automatically with no human intervention..
Thanks in advance for any help.

Tricky one?

 

[jindon]

Hi
try
Sheet_Calculate

Private Sub Worksheet_Calculate()
If Range("a1").Value > 200 Then
    'Your Macro
End If
End Sub

 

[mrclox]

That works but it the macro goes into a loop eventually crashing excel , is there a way for this to trigger the macro just once until the criteria of >200 goes false then true again
thanks for your help so far!

 

[al_b_cnu]

Hi,

Try

Private Sub Worksheet_Calculate() 
If Range("a1").Value > 200 Then 
    Application.Enableevents=False
    'Your Macro 
    Application.Enableevents=True
End If 
End Sub

 

[mrclox]

Thank you people , the internet is a wonderful thing.. you have solved my problem, when my automated horse racing spreadsheet makes its first million i will treat you all!