DataBodyRange.ClearContents target value based on TABLE's starting cell

Question

Boy was I confused. I just had all my formatting go crazy on a table and finally figured out why. Is this a bug or expected behavior with using ClearContents on the DataBodyRange within a table?

If I create a simple table, and, in a Worksheet_Change sub check for changes on a particular column in order to apply formatting, and later run Sheet1.ListObjectes("Table1").DataBodyRange.ClearContents to clear the contents--but not the formatting--of the table, the Worksheet_Change sub gets triggered (as expected) but... I would have expected each cell to be passed as the TARGET to the Worksheet_Change and then get any column-specific formatting applied according to the sub's code.

Instead, the column that corresponds to the starting column of the table seems to be considered the Target.Column. I guess I sort of get that behavior. Don't like it, but I get it. EXCEPT it doesn't seem to apply for the columns that are specifically checked for and formatted (which makes it even more surprising and hard to deal with).

In the below code, if a 7 column table starts at column 1, when I run ClearContentsTest, I get: green, red, cyan, green, green, green. (The Worksheet_Change get's triggered, and EVERYTHING is treated as Target.Column=1 except for columns 2 and 3. I'm not sure why the IF statement saw column 2 and 3 as Target.Column = 2 and Target.Column = 3 since DataBodyRange.ClearContents is seemingly causing everything to be treated as Column 1 as can be seen by all the others going green)

Now, move the entire table over so it starts at column 2, I get red, cyan, red, red, red, red. Everything is seen as target.column=2 (except target.column = 3 still fires and gets its formatting to cyan)

Move it to start at column 3, I get cyan, cyan, cyan, cyan, cyan, cyan, cyan.

Am I missing something, or can you simply not dependably use DataBodyRange.ClearContents on a sheet when formatting per column is done in a Worksheet_Change sub since checking columns depends on the table position and...?

`Private Sub Worksheet_Change(ByVal Target As Range)
            If Target.Column = 1 Then
                Range(Target.Address).Font.Color = vbGreen
            ElseIf Target.Column = 2 Then
                Range(Target.Address).Font.Color = vbRed
            ElseIf Target.Column = 3 Then
                Range(Target.Address).Font.Color = vbCyan
            End If
End Sub



Sub ClearContentsTest()
 Sheet1.ListObjects("Table2").DataBodyRange.ClearContents
End Sub

Tim Williams Tim Williams · Accepted Answer · 2021-05-06T05:51:07

ClearContents is a single operation on a multi-cell range, so that range is what gets passed to Target. If you just change one cell then Target is one cell. It's up to you to manage the difference in the event handler. Range.Column is a shortcut for Range.Cells(1).Column

FYI Range(Target.Address) is a long way around of writing Target (and could in some circumstances lead to you working with a different range than you intended)

Example of managing multi-cell Target range:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, c As Range

    Set rng = Application.Intersect(Target, Me.Range("A:C"))
    If Not rng Is Nothing Then
        For Each c in rng.cells
            c.interior.color = Array(vbGreen, vbRed, vbCyan)(c.column-1) 
        Next c
    End if
           
End Sub

DataBodyRange.ClearContents target value based on TABLE's starting cell

1 Answers