Run a Macro When a Specific Cell Changes in Excel

Sections:

Run Macro When a Cell Changes (Method 1)

Run Macro When a Cell Changes (Method 2)

Run Macro when a Cell in a Range of Cells is Changed

Notes

Run Macro When a Cell Changes (Method 1)

This works on a specific cell and is the easiest method to use.

Go to the VBA Editor (Alt + F11) and double-click the name of the spreadsheet that contains the cell that will change or just right-click the worksheet tab and click View Code. In the window that opens, select Worksheet from the left drop-down menu and Change from the right drop-down menu.
You should now see this:

For more info on creating macros, view our tutorial on installing a macro in Excel.

Excel will actually run the code that we input here each time a cell within the worksheet is changed, regardless of which cell is changed. As such, we need to make sure that the cell that we want to check is the one that the user is changing. To do this, we use an IF statement and, in this example, Target.Address.

Sample code:

This checks if cell A1 was the cell that was changed and, if it was, a message box appears.

This is a very simple and easy-to-use template. Just change $A$1 to whichever cell you want to check and then put the code that you want to run inside the IF statement.

$A$1 refers to cell A1 and the dollar signs are just another way to reference a cell using what's called an absolute reference. You will always need to include the dollar signs in the cell reference when you use this method.

Target.address is what gets the address of the cell that was just changed.

Get the value of the Cell

You will probably want to reference the new value of the cell that was changed and you do that like this:

Target.Value

To output the new value of the cell in the message box, I can do this:

Target is the object that contains all of the information about the cell that was changed, including its location and value.

In the below examples you can also use Target to get the value of a cell the same way as here, though it may not be covered in those sections.

Run Macro When a Cell Changes (Method 2)

(Read the Method 1 example for a full explanation of the setup for the macro. This section covers only the code for the second method.)

The second way to check if the desired cell was changed is a little bit more complicated, but it's not difficult.

This method uses Intersect(Target, Range("A1")) to find out if the desired cell is being changed.

Just change A1 to the cell that you want to check. For this method, you do not need to include dollar signs around the range reference like you did for method 1.

This method uses the Intersect() function in VBA which determines if the cell that was edited "intersects" with the cell that we are checking.

Reference the value of the cell the same way as the first example.

Run Macro when a Cell in a Range of Cells is Changed

This uses the same syntax as the previous example but with a slight modification.

Here, we want to check if a cell that is within a range of cells has been changed; it could be one of many cells.

This uses Intersect() just like the previous example to find out if one of the desired cells has been changed.

In this example, the message box appears if any cell in the range A1:A5 is changed. All you have to do is change A1:A5 to the desired range.

Notice that you also don't need dollar signs in the range reference here like you did in the very first example.

Reference the value of the cell the same way as the first example.

Notes

Knowing which cell was edited and what the new value is for that cell is, as you can see, quite simple. Though simple, this will allow you to make interactive worksheets and workbooks that update and do things automatically without the user having to click a button or use keyboard shortcuts to get a macro to run.

Download the workbook attached to this tutorial and play around with the examples and you will understand them in no time!