Run a Macro when a cell changes - Feature Image

How to Tell if a Cell Changed with VBA

Joseph Advanced, VBA 0 Comments

There are times when you want to run a macro when a cell changed. Maybe you want to do some special calculation when a cell changes. Or maybe you’d like to change the font to all uppercase. Or maybe you want to present your users with more meaningful data validation messages. Whatever the case may be, you can use Excel’s Worksheet_Change event in VBA to help accomplish your automation needs.

What is a Worksheet_Change Event?

The Worksheet_Change event is a special event that happens in Excel when a cell (or multiple cells) has changed in a specific worksheet. This includes when a cell is created, updated, or deleted.

This does not include changes like:

  • Formatting changes (font size, cell size, font/cell color, conditional formatting, etc.)
  • A cell changed because the calculation was updated (this is a different event called the Worksheet_Calculate event)
  • Selecting a cell (this is another event called Worksheet_SelectionChange event)

How do I use this Event?

Open up the VB Editor (Alt+F11 in Windows or Tools->Macros->Visual Basic Editor on Mac). In the Project window, you’ll see where the workbook and worksheets are:

VBE Project Window

Double-click on the Sheet1 object and paste the following code:

This is a very basic example of how to start using the Worksheet_Change event. This method is what’s called an Event Handler, which simply means that “this is the method I want to use for handling when X event happens.”

Whenever a cell changes as we described above, Excel will execute the Worksheet_Change event and pass in the cells that were changed through the target object. We use this target object to take a look at which cells were affected.

To invoke this event, go to the Sheet1 worksheet and make a change to a cell. Then, in the VBE Immediate Window (Ctrl+G on Windows or in Mac this window should always be visible) you will see some text appear each time the event is fired. Here’s an example:

Cell Changed - Worksheet_Change Event

Notice that whenever I change a cell, the Immediate Window shows some text based on the cell I changed. Also note that if I change multiple values at the same time, like cells A1:A2, then the target object contains that specific range and not just a single cell or array of cells.

It’s important to note that you can work with a range of cells within the same target object that is passed in to the event handler. We’ll take a look at this in the following sections.

How do I check if the Cell Changed within a Specific Range?

The target object is simply a range object that describes which cell changed (or set of cells). As shown before, you can get either a single cell, or multiple cells passed into the range (even several range areas, but that’s a topic for another post).

When you want to see if the items that changed fall within another range, you can use the following code:

Here is an example of how this works:

Cell Changed - Worksheet_Change Event - Check Range

I use the Intersect() function to determine if the target range is within another range that I defined "A1:B3". The result of Intersect will be the range of cells that intersected, if any. The Intersect() function returns Nothing when no intersection is found. This is why we check for that in the If statement.

Notice that when I select A2:C4, the target object has the range A2:C4, but the intersection range has A2:B3. This is an important distinction to make when working with an intersection. If you used the target range instead of the intersection range, you may end up working with the wrong data (i.e. cells C2:C4 in this case).

How to avoid infinite loops

Now that you know how to monitor changes in your worksheet and act on them, let’s consider what would happen if you make a change to a cell within the Worksheet_Change event.

Consider the following code:

In the For loop, I go through each cell that was effected and prepend the cell with some text.

However, changing the cell value will cause the Worksheet_Change event to fire again, which will take the new value of the text and again prepend it with some more text, and on and on this will go until either Excel crashes or you close the app.

Cell Changed - Infinite Loop

To avoid this, we have to temporarily disable events from firing, make the change to the cell, then re-enable the events.

Application.EnableEvents is a boolean property that you can read / write. When we set this to False, any events that would normally happen (like a Worksheet_Change event) will be suppressed. When we turn this back to True, we will get the events to happen as we expect.

Are you ready to master Excel Dashboards?

Learn Excel Dashboard Course Power BI Webinar

Still not convinced? Check out my review of the course!