My macro wont run automatically

[Goody61865]

I can initiate in manual and it works just fine but I want it to run automatically when a cell value is entered greater than zero is entered.

Here is my code:

Option Explicit
Sub Worksheet_calculate1()
Dim FormulaRange As Range
Dim NotSentMsg As String
Dim MyMsg As String
Dim SentMsg As String
Dim MyLimit As Double
Dim FormulaCell As Range


NotSentMsg = "Not Sent"
SentMsg = "Sent"
MyLimit = 0
Set FormulaRange = Me.Range("D56")
On Error GoTo EndMacro:
For Each FormulaCell In FormulaRange.Cells
With FormulaCell
If IsNumeric(.Value) = False Then
MyMsg = "Not numeric"
Else
If .Value > MyLimit Then
MyMsg = SentMsg
If .Offset(0, 1).Value = NotSentMsg Then
Call Mail_with_outlook5
End If
Else
MyMsg = NotSentMsg
End If
End If
Application.EnableEvents = False
.Offset(0, 1).Value = MyMsg
Application.EnableEvents = True
End With
Next FormulaCell
ExitMacro:
Exit Sub
EndMacro:
Application.EnableEvents = True
MsgBox "Some Error occurred." _
& vbLf & Err.Number _
& vbLf & Err.Description
End Sub


I am sure it is something simple that I am missing. Thanks for the help

 

[Goody61865]

It worked when I fired it manually but it did not work when I entered a number greater than zero in the cell (automatically)

 

[VoG]

I think that you should be using

Private Sub Worksheet_Change(ByVal Target As Range)

 

[Goody61865]

Works perfectly!!! Thanks so much