Hello guys
I have so many similar values in column O so I have this codes below to highlight all similar values.
the codes works great in rows with lower than 150 but it gets error when I have more than 500 rows.
I got stacked in below underlined code.
Sub Highlight_Duplicate_Entry()
Dim cel As Range
Dim myrng As Range
Dim clr As Long
Set myrng = Range("O2:O" & Range("O65536").End(xlUp).Row)
myrng.Interior.ColorIndex = xlNone
clr = 3
For Each cel In myrng
If Application.WorksheetFunction.CountIf(myrng, cel) > 1 Then
If WorksheetFunction.CountIf(Range("O2:O" & 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
cel.Font.Color = -vbWhite * (77 * (cel.Interior.Color Mod &H100) + 151 * ((cel.Interior.Color \ _
&H100) Mod &H100) + 28 * ((cel.Interior.Color \ &H10000) Mod &H100) < 32640)
End If
Next
End Sub
please help.
Thanks,
Bookmarks