highlight duplicates permanently

[overbet]

I have a large sheet that contains a lot of duplicate values that need to be looked at then possibly removed. When I use the conditional format tool to highlight duplicates I can manually go through and determine if they should be kept or deleted. The problem is because the sheet is so large when I delete a cell Excel becomes unresponsive for a few minutes. I tried turning calculation off but that doesnt fix it. Is is possible to highlight the duplicate values permanently without conditional formatting?

 

[Gerald Higgins]

There are ways of highlighting duplicate values without conditional formatting.

For example, with a very simple set of data like this in cells A1:A3 . . .

A
B
A

Insert a formula like this into B1 and copy down

=if(countif(a$1:a$3,a1)>1,"Duplicate","")

 

[SpillerBD]

In both cases, their will be a big calculation hit if your row count is high.
With the Conditional format, you could let conditional format work. Then use auto-filter to filter for that color, then apply a regular fill to those cells. Then remove the conditional formatting to regain performance.
The same would be true to the formula suggested, but you have better control over when the calculation runs.

The formula method Gerald Higgins suggest also is beneficial because formatting is not data. Having "Duplicate" listed for each row is far more useful as it can be used in other reporting tool such as Pivot Tables.
Copy>Paste Special>Values may occasionally have use too.

 

[AC PORTA VIA]

you can use this code (in this case column I)

Sub Highlight_Duplicate()
        Dim cel As Variant
        Dim myrng As Range
        Dim clr As Long

        Set myrng = Range("I2:I" & Range("i65536").End(xlUp).Row)
        myrng.Interior.ColorIndex = xlNone
        clr = 36
        For Each cel In myrng
           If Application.WorksheetFunction.CountIf(myrng, cel) > 1 Then
              If WorksheetFunction.CountIf(Range("I2:I" & cel.Row), cel) = 1 Then
                 cel.Interior.ColorIndex = clr
                 clr = clr + 1
              Else
                 cel.Interior.ColorIndex = myrng.Cells(WorksheetFunction.Match(cel.Value, myrng, False), 1).Interior.ColorIndex
              End If
          End If
       Next
       
    End Sub