My User Defined Function in Worksheet_Change event doesn't work if I select entire table and clear

rodsan724 

Loop through the affected cells:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    If Not Intersect(Range("Table1[Sign]"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each rng In Intersect(Range("Table1[Sign]"), Target)
            If rng.Value = "" Then
                rng.Offset(0, -1).ClearContents
                If Not rng.Offset(0, -1).Comment Is Nothing Then
                    rng.Offset(0, -1).Comment.Delete
                End If
                rng.Offset(0, -2).ClearContents
            Else
                rng.Offset(0, -1).Value = "RS"
                If rng.Offset(0, -2) = "" Then
                    rng.Offset(0, -1).AddComment Text:="hello world"
                End If
                Target.Offset(0, -2).Value = Date
            End If
        Next rng
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub