Run a Macro When a Specific Cell Changes in Excel

Add to Favorites
Author:

Run a macro in Excel when a specific cell is changed; this also covers when a cell within a range of cells is changed or updated.

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:

If Target.Address = "$A$1" Then

 

    MsgBox Target.Value

 

End If

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.

If Not Intersect(Target, Range("A1")) Is Nothing Then

 

    MsgBox "Hi!"

   

End If

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.

If Not Intersect(Target, Range("A1:A5")) Is Nothing Then

 

    MsgBox "Hi!"

 

End If

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!


Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
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 ...
Run a Macro when a User Does Something in the Workbook in Excel
Tutorial: How to run a macro when a user does something within the Workbook in Excel, such as openi...
Automatically Run a Macro When a Workbook is Opened
Tutorial: How to make a macro run automatically after a workbook is opened and before anything els...
Automatically Run a Macro so Many Seconds, Minutes, or Hours After an Excel Workbook has been Opened
Macro: Run a macro after a certain amount of time has passed since the Excel workbook was ope...
Stop Excel Events from Triggering when Running a Macro
Tutorial: Create a macro that will run without triggering events, such as the Change event, or Activ...
Automatically Run a Macro at a Certain Time - i.e. Run a Macro at 4:30PM every day
Macro: Automatically run an Excel macro at a certain time. This allows you to not have to worry a...
Tutorial Details
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course