If cell I20 is greater than 100 then "A MsgBox warning" is needed.

Formula in I19 is =SUM(I5:I18)
Formula in AE19 is =SUM(AE4:AE18)

Formula in I20 is =SUM(I19,AE19)

I have these two (Change & Calculate) macros in the sheet module. (below)
Both work if I20 100, I get a msgbox warning from each macro.

I expect to add more If statements in the SheetChange macro as well as there may also be more Calculations to monitor in others cells on the sheet that have formulas.

What is the best practice in this situation?
All in the Change macro or all in the Calculate macro?

It takes an entry on the sheet (Columns I or AE) to affect the value of I20, therefore the Change Event seems to work fine.

It takes a calculation on the sheet to affect the I20 value and therefore the Calculate Event seems to work fine.

Also at issue is the values in columns I and AE are percentages (%). What is the proper method to format/alter the cells/column/formulas to prevent 80 from reading out as 800%?


Thanks.

Howard

Here are the two codes that are working for me now.

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("I20") 100 Then
MsgBox "Funds allocated among properties cannot be greater than 100%" & vbCr & _
"Review values in Columns I and AE"
End If



If Intersect(Target, Range("B4:R18")) Is Nothing Or Target.Cells.Count 1 Then Exit Sub

If InStr(1, Target, ",") 0 Or InStr(1, Target, ";") 0 Then

Application.EnableEvents = False

With Target
.Replace What:=",", Replacement:="", LookAt:=xlPart
.Replace What:=";", Replacement:="", LookAt:=xlPart
End With

Application.EnableEvents = True

Else

Application.EnableEvents = True
Exit Sub

End If

MsgBox "Comma's or Semi-Colon's removed from " & Target.Address(False, False)


End Sub



Private Sub Worksheet_Calculate()

If Range("I20").Value 100 Then
MsgBox "Funds allocated among properties cannot be greater than 100%" & vbCr & _
"Review values in Columns I and AE"
End If

End Sub