Search code examples
excelvbatablename

Error 424 : Object required _ setting Table as a range

The below code is for conditional formatting of selected rows within a table.
I tried to replace the range related to the table by the table name reference in order to allow the change of the range automatically when new data is added. But when I tried that, it shows the following message:

enter image description here

The row in the code highlighted is the one with red as below.
Any help will be appreciated. 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("Table1")   
[COLOR="#FF0000"]If Not Intersect(Target, tbl.DataBodyRange.Select) Is Nothing Then  [/COLOR] 
        Dim Cell As Range
        Range("A4").NumberFormat = "@"
        If Target.Count > 1 And Target.Count < 50 Then
        StopCode
        For Each Cell In Selection
        Range("A4").Value = Range("A4").Value & "(" & Cell.Row - 21 & ")"
        Next Cell
        ResetCode
        Else
        Range("A4").Value = "(" & Target.Row - 21 & ")"
        End If
Else
Range("A4").ClearContents
End If
End Sub
Solution

  • Try the following instead, without the use of Select...

    If Not Intersect(Target, tbl.DataBodyRange) Is Nothing Then