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