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 l...

Formulas and Functions

Macros and VBA

HansVogelaar

Aug 27, 2023

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

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

Forum Discussion

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