Worksheet.Change Event

Occurs when cells on the worksheet are changed by the user or by an external link.

Syntax

expression.Change(Target)

expression   A variable that represents a Worksheet object.

Parameters

Name Required/Optional Data Type Description
Target Required Range The changed range. Can be more than one cell.

Return Value
Nothing

Remarks

This event doesn't occur when cells change during a recalculation. Use the Calculate event to trap a sheet recalculation.

Example

This example changes the color of changed cells to blue.

Visual Basic for Applications
Private Sub Worksheet_Change(ByVal Target as Range)
    Target.Font.ColorIndex = 5
End Sub


MVP icon The following code example was provided by MVP Bill Jelen. Bill is the author of 24 books on Microsoft Office Excel. He is a regular guest on TechTV with Leo Laporte and the host of MrExcel.com, which includes more than 300,000 questions and answers about Excel.

The following code example verifies that, when a cell value changes, the changed cell is in column A, and if the changed value of the cell is greater than 100. If the value is greater than 100, the adjacent cell in column B is changed to the color red.
Visual Basic for Applications
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Column = 1 Then
        ThisRow = Target.Row
        If Target.Value > 100 Then
            Range("B" & ThisRow).Interior.ColorIndex = 3
        Else
            Range("B" & ThisRow).Interior.ColorIndex = xlColorIndexNone
        End If
    End If
End Sub

After a cell on the worksheet is changed by user or external link? change change event