How To Tell If A Cell Changed With VBA | Spreadsheets Made Easy

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)

Does this article help you? If so, please consider supporting me with a coffee ☕️

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:

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

Option Explicit Public Sub Worksheet_Change(ByVal target As Range) Debug.Print "Something changed in cell " & target.Address(0, 0) End Sub

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:

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:

Option Explicit Public Sub Worksheet_Change(ByVal target As Range) Dim intersection As Range Set intersection = Intersect(target, Range("A1:B3")) If Not intersection Is Nothing Then Debug.Print "Cells that changed: " & target.Address(0, 0), _ vbTab & "Intersection at " & intersection.Address(0, 0) End If End Sub

Here is an example of how this works:

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:

Option Explicit Public Sub Worksheet_Change(ByVal target As Range) Dim intersection As Range Set intersection = Intersect(target, Range("A1:B3")) If Not intersection Is Nothing Then Dim cell As Range For Each cell In intersection cell.Value = "Text changed to: " & cell.Value Next cell End If End Sub

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.

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

Option Explicit Public Sub Worksheet_Change(ByVal target As Range) Dim intersection As Range Set intersection = Intersect(target, Range("A1:B3")) If Not intersection Is Nothing Then Dim cell As Range Application.EnableEvents = False For Each cell In intersection cell.Value = "Text changed to: " & cell.Value Next cell Application.EnableEvents = True End If End Sub

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.