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.

Macro Installation Location: Worksheet

Keywords: sort data sorting excel one column next subset adjacent data set table advanced

Pop-Up Message Box When a Range of Cells Reaches a Certain Average

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

Email:
Pass: