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