Find , mark and filter duplicates at ListObject (column of Dynamic Table) VBA

[Rita F]

Hi
I am trying to find, mark and filter duplicates at ListObject (column of Dynamic Table) VBA without success.
The Following script works great for the regular range, I made some changes and need it for the list object.
I will very much appreciate your assistance

Sub Duplicates()





ActiveSheet.Shapes("shape3").Select 'change to whatever your shape is called
If Selection.ShapeRange.Fill.Visible = msoFalse Then
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 40
Else

Selection.ShapeRange.Fill.Visible = msoFalse

End If


Dim Rng As Range
Dim cel As Range

'Test for duplicates in a single column
'Duplicates will be highlighted in red


Set Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))


For Each cel In Rng
If WorksheetFunction.CountIf(Rng, cel.Value) > 1 Then

cel.Interior.ColorIndex = 3
End If
Next cel

Range("B:J").Select

ActiveSheet.Range("$B$1:$J$1").AutoFilter Field:=1, Criteria1:=RGB(255, 0 _
, 0), Operator:=xlFilterCellColor
ActiveWindow.SmallScroll Down:=-9
ActiveSheet.Range("$B$1:$J$1").AutoFilter Field:=9, Criteria1:="<>0", _
Operator:=xlAnd



End Sub

 

[Peter_SSs]

Would this work for you for, say, the second column of the ListObject?

Sub Dupes()
  With ActiveSheet.ListObjects(1).ListColumns(2).DataBodyRange
    .FormatConditions.AddUniqueValues
    .FormatConditions(1).DupeUnique = xlDuplicate
    .FormatConditions(1).Interior.Color = vbRed
  End With
End Sub

 

[Rita F]

Would this work for you for, say, the second column of the ListObject?

Sub Dupes()
  With ActiveSheet.ListObjects(1).ListColumns(2).DataBodyRange
    .FormatConditions.AddUniqueValues
    .FormatConditions(1).DupeUnique = xlDuplicate
    .FormatConditions(1).Interior.Color = vbRed
  End With
End Sub

Great Solution, Thank you!

 

[Peter_SSs]

Great Solution, Thank you!

You're welcome. Thanks for the follow-up.