How to exec a macro every time a value in a cell change?

[earp_]

Hi,
thanks again for your help. I have another question.
Right now my macro change every second in this way...

Sub Autpen()
Application.OnTime Now, "myMacro"
End Sub

Sub BAuto_Close()
Application.OnTime mdNextTime1, "myMacro", , False
End Sub

Sub usaShares()
......
......
mdNextTime1 = Now + TimeValue("00:00:01")
Application.OnTime mdNextTime1, "myMacro"

Application.ScreenUpdating = True

End Sub

Is it possible to run myMacro everytime the value in B2 of sheet1 changes?
the value in B2 is in this format

16:49:59

Thanks in advance...

 

[earp_]

Right now my macro change every

my macro changes every..

 

[Jon von der Heyden]

Perhaps use a worksheet_selectionchange event, e.g.:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("B2")) Is Nothing Then
    MyMacro
End If

End Sub

Applied to the sheet of interest of course.

HTH

Jon

 

[earp_]

Perhaps use a worksheet_selectionchange event, e.g.:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("B2")) Is Nothing Then
    MyMacro
End If

End Sub

Applied to the sheet of interest of course.

HTH

Jon

Do I need to put the private sub in my module or in ThisWorkBook ?
What is target?

 

[earp_]

I tried to put the code into Sheet1 and it doesn't work.
I tried also with ThisWorkbok but it doesn't work either.

 

[earp_]

I found this one

Private Sub Worksheet_Change(ByVal Target As Range)
Static oldVal As Variant
If Intersect(Target, Union(Sheet1.Range("B2"), Sheet1.Range("B2").Precedents)) Is Nothing Then Exit Sub
If [B3] = oldVal Then Exit Sub

oldVal = Sheet1.Range("B2")
'The value changed. Do your stuff
MsgBox "B2 changed!"
End Sub

but where shul i put it?

 

[Jon von der Heyden]

I tried to put the code into Sheet1 and it doesn't work.
I tried also with ThisWorkbok but it doesn't work either.

What triggers a value change in B2? Recalculation? Perhaps you need to trigger your macro from the calculate event:

Private Sub Worksheet_Calculate()
    MyMacro
End Sub

Which needs to be added to the appropriate sheet (presume Sheet1)

HTH
Jon

 

[earp_]

ok thanks