Email macro works 1 time on excel sheet and stops

[Allkman]

I want a macro to send me email once the value is "1" on my cells range.
This macro only runs 1 time, and then stops.
Need to change "ExitMcro" and "EndMacro"

Also i would like it to run only if new cell gets value "1"

Is there a solution to have macro work once "empty" cell is changed to "1".
I have 28 rows with live data witch changes every second. So i have my "1" changed to "empty" many times per day. That can create a lot of spam emails to me.

My code in excel file, paste it to visual basic,
sheet code and Module code.

[rollis13]

How about using Worksheet_Change instead of Worksheet_Calculate ?
Option Explicit
'Private Sub Worksheet_Calculate()
Private Sub Worksheet_Change(ByVal Target As Range) '<== new

Dim FormulaRange As Range
Dim NotSentMsg As String
Dim MyMsg As String
Dim SentMsg As String
Dim MyLimit As Double

If Not Intersect(Range("R4:R31"), Target) Is Nothing Then '<== new
NotSentMsg = "Not Sent"
SentMsg = "Sent"
'Above the MyLimit value it will run the macro
MyLimit = 0
'Set the range with the Formula that you want to check
Set FormulaRange = Me.Range("R4:R31")
On Error GoTo EndMacro:
For Each FormulaCell In FormulaRange.Cells
With FormulaCell
If IsNumeric(.Value) = False Then
MyMsg = "--"
Else
If .Value > MyLimit Then
MyMsg = SentMsg
If .Offset(0, 1).Value = NotSentMsg Then
Call Mail_with_outlook1
End If
Else
MyMsg = NotSentMsg
End If
End If
Application.EnableEvents = False
.Offset(0, 1).Value = MyMsg
Application.EnableEvents = True
End With
Next FormulaCell
End If '<== new
ExitMacro:
Exit Sub
EndMacro:
Application.EnableEvents = True
MsgBox "Some Error occurred." _
& vbLf & Err.Number _
& vbLf & Err.Description
End Sub