This macro will display a message box when the numbers within a range of cells reaches an average of 5. The current macro will display a pop-up message in excel when the average number in cells A1:C5 reaches exactly 5. The average number can be changed to any desired number and so can the message contained in the pop-up message box. To change the average number, change 5 to whatever you want in this line of code: Average(rng) = 5 Then and change the 5 in the message box here: rng.Address & " = 5".
This allows you to keep better track of a constantly updating worksheet. If you have many linked cells and you want to be notified when a set of cells hits a certain average, this is when you might use this macro.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Set rng = Range("A1:C5")
If Not Intersect(Target, rng) Is Nothing Then
If Application.WorksheetFunction. _
Average(rng) = 5 Then
MsgBox "The average of " & _
rng.Address & " = 5"
End If
End If
Set rng = Nothing
End Sub