SheetChange Event

Private Sub Object_SheetChange(ByVal Sh As Object, ByVal Target As 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