Occurs when cells on the worksheet are changed by the user or by an external link.
expression A variable that represents a Worksheet object.
|Target||Required||Range||The changed range. Can be more than one cell.|
This event does not occur when cells change during a recalculation. Use the Calculate event to trap a sheet recalculation.
The following code example changes the color of changed cells to blue.
Private Sub Worksheet_Change(ByVal Target as Range) Target.Font.ColorIndex = 5 End Sub
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.
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
The following code example sets the values in the range A1:A10 to be uppercase as the data is entered into the cell.
Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1:A10")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub Application.EnableEvents = False 'Set the values to be uppercase Target.Value = UCase(Target.Value) Application.EnableEvents = True End Sub