I have the below code that works great but only works when the event is changed. Once the event is triggered and the CALL macro is launched I can not get it to work again until I get it to fail. Is there a way to reset the event enabler to recalculate each time the code is launched.
Code
[/SIZE]Private Sub Worksheet_Calculate() Dim FormulaRange As Range Dim NotSentMsg As String Dim MyMsg As String Dim SentMsg As String Dim MyLimit As Double NotSentMsg = "No EMAIL" SentMsg = "Send EMAIL" 'Above the MyLimit value it will run the macro MyLimit = 0 'Set the range with the Formula that you want to check Set FormulaRange = ThisWorkbook.Sheets("Forecast LH 100 Summary").Range("AU85") 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 Send_Email End If Else MyMsg = NotSentMsg End If End If Application.EnableEvents = False .Offset(0, 1).Value = MyMsg Application.EnableEvents = True End With Next FormulaCellExitMacro: Exit SubEndMacro: Application.EnableEvents = True MsgBox "Some Error occurred." _ & vbLf & Err.Number _ & vbLf & Err.Description End Sub[size=14]
[/SIZE]