How to Use the Worksheet_Change Event to Run a Macro in Excel

Excel macros are powerful tools that can automate tasks and save time. However, sometimes you may want to trigger a macro based on a condition, such as when a cell value changes to a certain value. For example, you may want to run a macro that calculates the total sales when the month number changes in a cell.

In this article, we will show you how to run a macro automatically if a cell changes to a certain value in Excel, using the Worksheet_Change event. We will also explain the basic theory behind this technique, and provide a detailed example with real data.

The Worksheet_Change event is a special type of event that occurs when a cell or a range of cells on a worksheet is changed by the user or by an external link. You can use this event to run a macro whenever a specific cell or a range of cells changes.

To use the Worksheet_Change event, you need to write a VBA code in the worksheet module, not in a standard module. A worksheet module is a code window that is associated with a specific worksheet. You can access it by right-clicking the sheet tab and choosing View Code.

The Worksheet_Change event has one parameter, called Target, which represents the range of cells that were changed. You can use the Target parameter to check if the cell or the range of cells that you want to monitor is part of the changed range. For example, you can use the Intersect function to test if the Target range intersects with a specific cell or range.

The Intersect function returns the range where two or more ranges overlap. If there is no overlap, it returns Nothing. For example, Intersect (Target, Range (“A1”)) returns the range A1 if the Target range includes A1, and returns Nothing otherwise.

You can use the Intersect function in an If statement to run a macro only if the Target range intersects with the cell or the range that you want to monitor. For example, the following code runs a macro named MyMacro if the cell A1 changes:

Private Sub Worksheet_Change (ByVal Target As Range)
  If Not Intersect (Target, Range (\"A1\")) Is Nothing Then
    MyMacro
  End If
End Sub

Note that you need to use the Not operator before the Intersect function, because the If statement evaluates to True if the Intersect function returns Nothing, and False otherwise.

Procedures

To run a macro automatically if a cell changes to a certain value in Excel, you need to follow these steps:

1. Write the macro that you want to run in a standard module. You can use the Macro Recorder to record your actions, or write the VBA code manually. For example, the following macro calculates the total sales by multiplying the quantity and the price in columns B and C, and puts the result in column D:

Sub CalculateTotalSales ()
  Dim LastRow As Long
  Dim i As Long
  'Find the last row with data in column B
  LastRow = Cells (Rows.Count, \"B\").End (xlUp).Row
  'Loop through each row from row 2 to last row
  For i = 2 To LastRow
    'Calculate the total sales by multiplying quantity and price
    Cells (i, \"D\").Value = Cells (i, \"B\").Value * Cells (i, \"C\").Value
  Next i
End Sub

2. Write the Worksheet_Change event code in the worksheet module where you want to monitor the cell change. You can use the Intersect function to check if the Target range includes the cell that you want to monitor, and use an If statement to run the macro if the cell value changes to a certain value. For example, the following code runs the CalculateTotalSales macro if the cell A1 changes to 1:

Private Sub Worksheet_Change (ByVal Target As Range)
  If Not Intersect (Target, Range (\"A1\")) Is Nothing Then
    'Check if the cell value is 1
    If Range (\"A1\").Value = 1 Then
      'Run the macro
      CalculateTotalSales
    End If
  End If
End Sub

3. Save the workbook as a macro-enabled workbook (.xlsm) and test the code by changing the cell value.

Example

To illustrate how to run a macro automatically if a cell changes to a certain value in Excel, let’s use the following example data:

We want to run a macro that calculates the total sales by multiplying the quantity and the price in columns B and C, and puts the result in column D. However, we only want to run the macro when the month number changes in cell A1. For example, if we enter 1 in cell A1, we want to run the macro for January, and if we enter 2 in cell A1, we want to run the macro for February, and so on.

To achieve this, we can use the following steps:

1. Write the macro that calculates the total sales in a standard module, as shown in the previous section.
2. Write the Worksheet_Change event code in the worksheet module where the data is located, as shown in the previous section.
3. Save the workbook as a macro-enabled workbook and test the code by changing the cell A1 value.

The following table shows the result of running the macro for different values of cell A1:

Other Approaches

There are other ways to run a macro automatically if a cell changes to a certain value in Excel, such as using the Worksheet_Calculate event, the Workbook_SheetChange event, or the Workbook_SheetCalculate event. However, these events have some limitations and drawbacks, such as:

• The Worksheet_Calculate event occurs when any formula on the worksheet is recalculated, not when a specific cell value changes. This means that the macro may run unnecessarily or not run at all, depending on the calculation mode and the dependency of the formulas.
• The Workbook_SheetChange event and the Workbook_SheetCalculate event occur when any cell or formula on any worksheet in the workbook is changed or recalculated, not when a specific cell value changes. This means that the macro may run unnecessarily or not run at all, depending on the calculation mode and the dependency of the formulas. Moreover, these events require writing the code in the ThisWorkbook module, not in the worksheet module, which may be less convenient or intuitive.

Therefore, using the Worksheet_Change event with the Intersect function is the most reliable and flexible way to run a macro automatically if a cell changes to a certain value in Excel.