Find the duplicate and create a pivot using VBA

[Tharabai]

Hi All,

I have used the below code to find the duplicate values in column Y. the code highlights the duplicate value in green color instead i want the duplicate values to be entered as "Duplicate" in column Z. And i need to create a pivot using VBA with the report filter as column Z value and column A, D as the row field value and column AB as the values.




Sub dups()Dim myrange As Range
Dim cel As Variant

Set myrange = Range("Y:Y")

myrange.Interior.ColorIndex = xlNone

For Each cel In myrange
If Application.WorksheetFunction.CountIf(myrange, cel) > 1 Then
cel.Interior.ColorIndex = 4
End If
Next
End Sub