Table of Contents
Run Macro When Cell Value Changes is a feature in Microsoft Excel that allows users to automate a macro’s execution, based on a change in the value of a specified cell. This feature is particularly useful for monitoring and controlling the flow of data or for triggering automated responses to changes in user input. It can also be used to update charts or other visualizations when the data changes.
You can use the following syntax in VBA to run a macro when a specific cell value changes:
Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Call MultiplyMacro
End If
End Sub
This particular example will cause the macro called MultiplyMacro to run when the value in cell A1 changes.
The following example shows how to use this syntax in practice.
Example: Run Macro When Cell Value Changes Using VBA
Suppose we create the following macro called MultiplyMacro that multiplies the values in cells A1 and B1 and displays the results in cell C1:
Sub MultiplyMacro()
Range("C1") = Range("A1") * Range("B1")
End Sub
For example, suppose we have the value 12 in cell A1 and the value 3 in cell B1.
If we run this macro, we’ll receive the following output:
Now suppose that we would like to automatically run this macro whenever the value in cell A1 changes.
To do so, we can right click on the sheet name and then click View Code:
In the code editing window that appears, we can paste the following code:
Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Call MultiplyMacro
End If
End Sub
The following screenshot shows how to do so in practice:
Now, each time we change the value in cell A1 the macro called MultiplyMacro will automatically run and perform multiplication using the new value in cell A1.
For example, suppose we change the value in cell A1 to 10. As soon as we change the value and press Enter, the macro will run:
The macro multiplies 10 by 3 and displays the result in cell C2.
Note: If you’d like to run the macro when any cell in a specific range changes, you can use the following syntax instead:
Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:B1")) Is Nothing Then
Call MultiplyMacro
End If
End Sub
This will cause the macro called MultiplyMacro to run if any cell in the range A1:B1 changes.