The worksheet event-handler procedures must be in the code module for that worksheet. Put them somewhere else, and they won’t work. You can quickly access that code window by right-clicking the worksheet’s tab and selecting the
Worksheet_Change event procedure
Change event triggers whenever any cell in the worksheet is changed. Excel uses the
Worksheet_Change event-handler procedure to trap the
Change event. The
Worksheet_Change procedure accepts
Range object) as the parameter which represents the cell that was changed. The following example displays a message box that shows the address of the
Private Sub Worksheet_Change(ByVal Target As Range) MsgBox Target.Address End Sub
Try making some changing in cells, every time you make changes, a message box displays the address of the cell that changed.
Monitor changes made to specific cell or range
Worksheet_Chnage procedure receives the
Range object which represents the changed cell(s). In this example, we compare the
Target with the given cell range
Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRange As Range Set MyRange = Range("A1:A10") If Not Intersect(Target, MyRange) Is Nothing Then MsgBox ("You've changed the " & Target.Address) End If End Sub
A popup message box appears when a change made in the given cell range:
Worksheet_Change Vs. Worksheet_Calculate
Worksheet_Change event procedure is not executed by a calculation change, for example, when a formula returning a different value. You must use the
Worksheet_Calculate event procedure to capture the changes to values in cells that contain formulas.
Worksheet_SelectionChange event procedure
Worksheet_SelectionChange event procedure executes when a cell is selected. The following code highlights the active cell with a red color every time a different cell is selected:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells.Interior.ColorIndex = 0 Target.Interior.ColorIndex = 3 End Sub
The first statement removes the background color for all cells in the worksheet. Next, the the active cell is shaded with red color.
Take some action when specific cells or ranges selected
In many cases, you need to execute a piece of code when certain cells or ranges selected. To accomplish this, we use the
Intersect method on the
Target (selected cell or range) and the range containing the specific cell to verify the
Target is one of the specific cells or ranges. If the Target is in the range containing the specific cells, you can execute the code.
The following code highlights the active cell with a red color every time a different cell is selected:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells.Interior.ColorIndex = 0 Dim MyRange As Range Set MyRange = Range("A1:A10") If Not Intersect(Target, MyRange) Is Nothing Then Target.Interior.ColorIndex = 3 End If End Sub