CODEWITHSUNDEEP
excelvba
Harin
Harin

Reputation: 57

Send email when value of a cell is 16, 64 or 120

I have a code whereby all my cells are conditional formulated and from one cell (B6) value will change.

I want email to be sent every time B6 cell value is 16, 64 and 120.

Currently it will only send at 16 and also it will start sending from any cells once it has reached to 16 target.

Option Explicit

Private Sub Worksheet_Calculate()
    Dim FormulaRange As Range
    Dim NotSentMsg As String
    Dim MyMsg As String
    Dim SentMsg As String
    Dim MyLimit As Double


On Error GoTo errHandler:

Sheet2.Unprotect Password:="1234"

    NotSentMsg = "Not Sent"
    SentMsg = "Sent"

    'Above the MyLimit value it will run the macro
    MyLimit = 15

    'Set the range with the Formula that you want to check
    Set FormulaRange = Me.Range("B6")

    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_Outlook_With_Signature_Html_1
                    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
Sheet2.Protect Password:="1234"
 '   MsgBox "Some Error occurred." _
  '       & vbLf & Err.Number _
   '      & vbLf & Err.Description

On Error GoTo 0
    Exit Sub
errHandler:
    MsgBox "An Error has Occurred  " & vbCrLf & _
           "The error number is:  " & Err.Number & vbCrLf & _
           Err.Description & vbCrLf & "Please Contact Admin"

End Sub

Upvotes: 0

Views: 72

Answers (1)

SJR
SJR

Reputation: 23081

Think you just need this. Your previous code would have run after every recalculation if B6>15. (This code will still run every time the sheet is re-calculated, but will only send the message if those values are hit.)

You only need the loop if you are considering a range of cells, such as B6:B10.

Private Sub Worksheet_Calculate()

Dim NotSentMsg As String
Dim MyMsg As String
Dim SentMsg As String

On Error GoTo errHandler:
Sheet2.Unprotect Password:="1234"

NotSentMsg = "Not Sent"
SentMsg = "Sent"

With Me.Range("B6")
    If Not IsNumeric(.Value) Then
        MyMsg = "Not numeric"
    Else
        If .Value = 16 Or .Value = 64 Or .Value = 120 Then
            MyMsg = SentMsg
            If .Offset(0, 1).Value = NotSentMsg Then
                Call Mail_Outlook_With_Signature_Html_1
            End If
        Else
            MyMsg = NotSentMsg
        End If
    End If
    Application.EnableEvents = False
    .Offset(0, 1).Value = MyMsg
    Application.EnableEvents = True
End With

Application.EnableEvents = True
Sheet2.Protect Password:="1234"
On Error GoTo 0
Exit Sub
errHandler:
MsgBox "An Error has Occurred  " & vbCrLf & _
       "The error number is:  " & Err.Number & vbCrLf & _
       Err.Description & vbCrLf & "Please Contact Admin"

End Sub

Upvotes: 1

Related Questions