SheetChange Event
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
Occurs when cells in any worksheet are changed by the user or by an external link.
Private Sub Object_SheetChange(ByValShAs Object, ByValTargetAs Range)
*Object * The name of the Spreadsheet object that you are trapping this event for.
Sh A Worksheet object that represents the sheet.
Target A Range object that represents the changed range.
Example
This example illustrates how to use the SheetChange event to perform conditional formatting on cells A1:10 in Sheet1 of Spreadsheet1.
Sub Spreadsheet1_SheetChange(Sh, Target)
Dim rngIntersect
Dim rngCondFormat
' Set a variable to the range to be conditionally formatted. In this
' case, the range is cells A1:A10 on Sheet1.
Set rngCondFormat = Spreadsheet1.Worksheets("Sheet1").Range("A1:A10")
' Check to see if the change was made on Sheet1.
If Sh.Name = "Sheet1" Then
' Set a variable to the intersection of the changed cell
' and the conditional formatting range.
Set rngIntersect = Spreadsheet1.RectIntersect(Target, rngCondFormat)
' Check to see if the changed cell intersects with the
' conditional formatting range.
If Not rngIntersect Is Nothing Then
' Format the target cell based on its value.
Select Case Target.Value
Case Is >= 25
Target.Font.Color = "Green"
Target.Font.Bold = True
Target.Font.Italic = False
Case Is >= 10
Target.Font.Color = "Blue"
Target.Font.Bold = False
Target.Font.Italic = True
Case Is < 10
Target.Font.Color = "Red"
Target.Font.Bold = True
Target.Font.Italic = False
End Select
End If
End If
End Sub