I am working on VBA for marking duplicates in Column J. I would like to mark duplicates with different color. However I would like also to skip empty cells. Marking duplicates is working but it is not skipping empty cells. Empty cells are getting marked with color as well.
Where might be a problem with my current code?
Sub MarkDuplicatesInKonserni() Dim cel As Variant Dim rng As Range Dim clr As Long Dim i Application.ScreenUpdating = False Application.Calculation = xlCalculationManual 'Sample Array of Columns Dim Col(10 To 10) As String Col(10) = "J" Set rng = ThisWorkbook.Worksheets("Main").Range(ThisWorkbook.Worksheets("Main").Cells(2, 10), ThisWorkbook.Worksheets("Main").Cells(ThisWorkbook.Worksheets("Main").Rows.Count, "J").End(xlUp)) 'Iterate through Columns For i = 10 To 10 rng.Interior.ColorIndex = xlNone clr = 3 For Each cel In rng If Application.WorksheetFunction.CountIf(rng, cel) > 1 Then If cel = Empty Then Else If Application.WorksheetFunction.CountIf(Range(Col(i) & "1:" & Col(i) & cel.Row), cel) = 1 Then cel.Interior.ColorIndex = clr clr = clr 1 Else cel.Interior.ColorIndex = rng.Cells(WorksheetFunction.Match(cel.Value, rng, False), 1).Interior.ColorIndex End If End If End If Next Next i Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub