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

Add to Favorites

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.

Where to install the macro:  Worksheet

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





Similar Content on TeachExcel
Pop-Up Message Box When a Cell Reaches a Certain Value or Contains Certain Text
Macro: This macro will display a message box in excel when a cell reaches a certain value or cont...
Loop through a Range of Cells in Excel VBA/Macros
Tutorial: How to use VBA/Macros to iterate through each cell in a range, either a row, a column, or ...
Excel Data Validation - Limit What a User Can Enter into a Cell
Tutorial: Data Validation is a tool in Excel that you can use to limit what a user can enter into a...
Simple Alternatives to UserForms
Tutorial: This tutorial covers a few simple ways to show pop-up windows to users that allows you to ...
Run a Macro when a User Does Something in the Worksheet in Excel
Tutorial: How to run a macro when a user does something in the worksheet, everything from selecting ...
Excel VBA MsgBox - Message Box Macro
Tutorial: Create a pop-up message box in Excel using VBA Macros. This allows you to show a message t...



How to Install the Macro
  1. Select and copy the text from within the grey box above.

  2. Open the Microsoft Excel file in which you would like the Macro to function.

  3. Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.  Or For other ways to get there, Click Here.

  4. On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.

  5. If the Macro goes in a Module, Click Here, otherwise continue to Step 8.

  6. If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.

  7. If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.

  8. Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.

  9. You are now ready to run the macro.


Similar Content
Pop-Up Message Box When a Cell Reaches a Certain Value or Contains Certain Text
Macro: This macro will display a message box in excel when a cell reaches a certain value or cont...
Loop through a Range of Cells in Excel VBA/Macros
Tutorial: How to use VBA/Macros to iterate through each cell in a range, either a row, a column, or ...
Excel Data Validation - Limit What a User Can Enter into a Cell
Tutorial: Data Validation is a tool in Excel that you can use to limit what a user can enter into a...
Excel Forum