VBA: How to Run Macro When Cell Value 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:

We can then close out of the VB Editor.

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.