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

I have this Table1 with fields DateTested, Initial, Sign.

The idea/goal is if user enters an x (any character for that matter) it will populate initial and date tested auto magically.

It seems like this works if I sign one line at a time just fine. However, I run into some issues when I try to drag Sign column to fill the column or when I select entire table and try to clear everything.

I get type-mismatched error. What am i doing wrong when it comes to bulk actions?

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

Formulas and Functions

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

3 Replies