Worksheet Change Event

Automatically Run Excel Macros When a Cell Changes

VBA Change to a Single Cell

In Excel a Worksheet Change Event is a trigger for a macro when a cell or group of cells change.  I will start out by showing how a change to a single cell can trigger an action.  The following will colour cell B2 Red whenever the cell changes.  The following uses the(ByVal Target As Range) line which uses the Variable named Target.  The Target is the Range which will trigger an action.  You assign the Range within the code itself.

The following YouTube video takes you the cell change event, both a single cell and multiple cells. The following Excel file goes with the video.

Change Cell.xlsm

Before you fill your boots with the following it is worth mentioning that when you employ the use of the VBA change events you lose the ability to undo in Excel.  Normally Excel keeps a record of a number of actions.

The VBA code to perform this action needs to go in the sheet object you want to perform the event.  If you wanted to put the code in Sheet1 then you would double click on the sheet you wish to run the code from.

The following is an example of Excel VBA coding you could put in Sheet1 or any of the other sheet objects.

In the example above you need to keep the $ (absolute sign) or the code will not work.  So when referencing a single cell the range reference needs to be absolute.

"$B$2”

The following VBA performs the same action as the above example.  It is a little more flexible if you wish to add to the range.  Once Inside the Worksheet Change Event, if the Target falls within the defined Range and the cell contents change, it will trigger an action inside VBA.

Disable Events

Occasionally one of the things you may wish to do with the cell that is changing is delete, copy, cut or some other action which triggers a circular loop.  For example, if you wanted to move a line to another sheet which met a condition, when the condition was met you would trigger the Change Event and when you deleted the row you would start another change event.  This second change event would cause a debug error.  To get around this you can turn Events off at the start of the procedure and turn them back on at the end of the procedure.

The line of code is;

Application.EnableEvents=False

and the following is an example of how it might be used.

The VBA  macro will copy the entire row from one sheet to another and delete the row which was just copied.  The example is shown in the file below.

VBA Worksheet Change Event Multiple Cells

When we want to perform an action when more than one cell is changed we can use the following VBA code to change a larger range. It focuses on shifting the range within the Target. The following is an example of a change event where if the cells from A2:A10 change the procedure will trigger an action.

VBA Double Click Event

A double click event in Excel VBA is self explanatory.  It will occur on double click of a cell in the Target range.  So if you have a range between C13 and O26 where you want to perform an action on Double click, the following should help.

The following Excel VBA macro will put the word False in Cell A1 before the file is saved.