trigger macro when a formula cell gives two kind of text-CodePudding

I want to create a macro that calls another macro when a specific range of cells that contain formulas give us as an output two specific strings. The formula is =IF(E15<=$G$5,"CHECK",IF(E15<=$F$5,"WARNING","OK")) and I want to trigger the macro every time there is an output "CHECK" and/or "WARNING". I want to trigger the macro every time the output is giving these two strings and not just once inside this range. The triggered range that I want to look at and contains the above formula is F8:F38. I can find something like this

   If Range("F8:F38").Value = "CHECK" Then
   Call email
End Sub

but this is not working in case there is a formula instead of just a string.

CodePudding user response：

You can use the Worksheet.Calculate event, and a loop:

Private Sub Worksheet_Calculate()
    Dim cell As Range
    For Each cell In Me.Range("F8:F38")
        If cell.Value = "CHECK" Or cell.Value = "WARNING" Then
            Email ' no need for Call
        End If
    Next
End Sub

If you only want to email when the values in F3:F38 have changed, then something like this:

In a regular module:

Public vals() As Variant ' public variable containing the 'old' values

In the ThisWorkbook module:

Private Sub Workbook_Open()
    ' populate vals when opening workbook
    vals = Me.Worksheets("Yourworksheetname").Range("F3:F38").Value
End Sub

In the sheet code module:

Private Sub Worksheet_Calculate()
    Dim cell As Range
    For Each cell In Me.Range("F8:F38")
        Dim counter As Long
        counter = counter   1
        
        If cell.Value = "CHECK" Or cell.Value = "WARNING" Then
            ' check if the 'new' value is different from the 'old' one
            If cell.Value <> vals(counter, 1) Then 
                Email
            End If
        End If
    Next
    
    vals = Me.Range("F8:F38").Value ' store the 'new' values 
End Sub