Msg Box based on a sum and another cell

[jaycatuk]

Hi
I want to be have a msg box pop up when the sum of a set of cells (range1) is more than the sum of another set of cells (range2). If range 1 > range 2 is true, then it needs to check a cell (A12) is not blank. If the cell is not blank, do nothing, if the cell is blank then pop up a message "you have too much".

So far i have:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("B26") > Range("B12") Then
MsgBox "WARNING: Total Development day costs are not enqual to total development days charged. Please provide explanation.", vbOK
End If
End Sub

Any help would be appreciated
Thanks
Jessica

 

[Lewiy]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If (Range("B26").Value > Range("B12").Value) And .Range("A12") = "" Then
    MsgBox "WARNING: Total Development day costs are not equal to total development days charged. Please provide explanation."
End If
End Sub

 

[jaycatuk]

Hi
Thanks for this. How do i now use it to sum a group of cells and compare that sum to the sum of another group
Thanks
Jessica

 

[Lewiy]

For the ranges, use something like:

Application.WorksheetFunction.Sum(Range("B1:B10"))

 

[jaycatuk]

Hi
Thanks for this, one last question. If i have multiple msg boxes that i want based on different criteria do i just put them one after another and they will work? ie

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
        
    If Range("B26").Value > Range("B12").Value And Range("E26").Value = "" Then
        MsgBox "WARNING: Total Development day costs are not enqual to total development days charged. Please provide explanation.", vbOK
    Else

    End If
End Sub

Private Sub Worksheet_Change2(ByVal Target As Range)
        
    If Range("E64").Value < 100000 And Range("B70").Value = "" Then
        MsgBox "WARNING: Add explaination of why contribution too little.", vbOK
    Else

    End If
End Sub