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?
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
- Selecting a cell (this is another event called
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
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
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
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
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
Notice that when I select
A2:C4, the target object has the range
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
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
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
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
we will get the events to happen as we expect.