How to run a macro when certain cells change in Excel

[soccer7allison]

Hi! I need to run a macro that a message box will appear when the value in multiple cells is not equal to zero. I have this right now, but I need the Range to be 4 different cells and not juxt the one! Can you help??

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As StringOn Error Resume Next
x = Sheets("FA_8").Range("H27").Value

If x = "" Or x = 0 Then
Exit Sub
Else
MsgBox ("This must net to zero or a comment must be entered in cell A34 below why this does not net to zero")
End If

 

[Joe4]

Welcome to the Board!

What are the four cells?

How are these four cells being updated? Manually? By a formula? Something else?

Are you wanting to check for all of them being equal to zero, and if any are not, return the message box?

 

[soccer7allison]

Welcome to the Board!

What are the four cells? - They are H27, N27, T27, and Z27

How are these four cells being updated? Manually? By a formula? Something else? The cells are updated by a formula

Are you wanting to check for all of them being equal to zero, and if any are not, return the message box?

- Yes! I want to see if each equals zero and if they are not return the message!

 

[Joe4]

Since they are updated by formulas, you will not be able to use a Worksheet_Change event. That only works for cells changed manually.
What exactly do the formulas look like? If they are pulling from other cells, which are updated manually, we can look at those and trigger the code when those change.

Otherwise, we need to use a Worksheet_Calculate event. The drawback there is Worksheet_Calculate cannot tell which cells value changes, it just runs any time any calculation on the entire sheet changes. So it often runs a lot more than it has to.

That code might look something like this:

Private Sub Worksheet_Calculate()

    Dim rng As Range
    Dim cell As Range
    Dim msg As String
    
'   Define range
    Set rng = Range("H27,N27,T27,Z27")


'   Loop through all cells
    For Each cell In rng
'       If value is not zero, add to msg string
        If cell.Value <> 0 Then
            msg = msg & vbCrLf & cell.Address(0, 0) & " is not zero"
        End If
    Next cell
    
'   If msg string is not empty, print to screen
    If Len(msg) > 0 Then
        MsgBox msg, vbOKOnly, "WARNING!"
    End If


End Sub

 

[soccer7allison]

Since they are updated by formulas, you will not be able to use a Worksheet_Change event. That only works for cells changed manually.
What exactly do the formulas look like? If they are pulling from other cells, which are updated manually, we can look at those and trigger the code when those change.

Otherwise, we need to use a Worksheet_Calculate event. The drawback there is Worksheet_Calculate cannot tell which cells value changes, it just runs any time any calculation on the entire sheet changes. So it often runs a lot more than it has to.

That code might look something like this:

Private Sub Worksheet_Calculate()

    Dim rng As Range
    Dim cell As Range
    Dim msg As String
    
'   Define range
    Set rng = Range("H27,N27,T27,Z27")


'   Loop through all cells
    For Each cell In rng
'       If value is not zero, add to msg string
        If cell.Value <> 0 Then
            msg = msg & vbCrLf & cell.Address(0, 0) & " is not zero"
        End If
    Next cell
    
'   If msg string is not empty, print to screen
    If Len(msg) > 0 Then
        MsgBox msg, vbOKOnly, "WARNING!"
    End If


End Sub

It worked perfect!!!!! THANK YOU!!!!!!

 

[Joe4]

You are welcome.