» Display message if column total is exceeded using VBA in Microsoft Excel

CATEGORY - General Topics in VBA VERSION - All Microsoft Excel Versions

Question:
If total of values in entry area cell 9 to cell 22 exceeds 7.5, a message is to be displayed

Answer:
Insert the following code in the appropriate modules.

Place the code below into the standard module



Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.Column < 4 Or Target.Column > 34 Then Exit Sub

   If Target.Row < 9 Or Target.Row > 22 Then Exit Sub

   If WorksheetFunction.Sum(Range(Cells(9, Target.Column), _

      Cells(22, Target.Column))) > 7.5 Then

      Beep

      MsgBox "Caution, the value of 7.5 is exceeded!"

   End If

End Sub