VBA Highlight Duplicates in a Column

[Neveidas]

Hello, as the thread title says it all, I am trying to create a VBA code to highlight duplicates in a single column, was thinking of adding on a delete duplicate code too but I'm afraid of deleting the wrong duplicate thus I am highlighting them first, below is the code that I am using currently, but when I delete the duplicates, the cells stay coloured. So is there a better way of having them highlighted so that when the duplicates are deleted, the cells won't be highlighted anymore? Thank you in advance for the help!

Private Sub DupeClean()
Application.ScreenUpdating = False

    Dim r As Range, rr As Range
    Dim Main As Worksheet
    Dim i As Long



    Set Main = ThisWorkbook.Sheets("Import")

    i = Main.Range("D" & Rows.Count).End(xlUp).Row
    Set rr = Main.Range("D2:D" & i)


    For Each r In rr
        If WorksheetFunction.CountIf(rr, r.Value) > 1 Then _
        r.Interior.ColorIndex = 3
    Next

Application.ScreenUpdating = True

End Sub

 

[Fluff]

You can use Conditional Formatting for that, it's on the Home Tab.
Click on Conditional Formatting, Highlight Cells Rules, Duplicate Values

 

[Neveidas]

You can use Conditional Formatting for that, it's on the Home Tab.
Click on Conditional Formatting, Highlight Cells Rules, Duplicate Values

hello, yes I was using that before but because it is like a daily routine and there are multiple sets to go through, so I figured it might be better if I can do it with a click of a button

 

[Zot]

hello, yes I was using that before but because it is like a daily routine and there are multiple sets to go through, so I figured it might be better if I can do it with a click of a button

Probably you just put the line to clear all (reset) colored interior before marking duplicate. So, after deleting the row, just run the macro again just like verification.

 

[Neveidas]

Probably you just put the line to clear all (reset) colored interior before marking duplicate. So, after deleting the row, just run the macro again just like verification.

hmmm, because the Highlighting of Duplicate is actually a call function, so if I were to run it for checking again, I would have to run the whole chunk again. The excel workbook is more or less a template, so would it be a better idea to have done what Fluff have mentioned? so that when others are using the template, it would have already check for duplicates once there are duplicates?

 

[Fluff]

You could record a macro applying the conditional format, so that it can be done on the click of button.

 

[Neveidas]

Alright, thank you guys for the advice!

 

[Fluff]

Glad we could help & thanks for the feedback.