(Solved) – Mark duplicates in range, skip empty cells


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


                        If Application.WorksheetFunction.CountIf(Range(Col(i) & "1:" & Col(i) & cel.Row), cel) = 1 Then
                            cel.Interior.ColorIndex = clr
                            clr = clr   1
                            cel.Interior.ColorIndex = rng.Cells(WorksheetFunction.Match(cel.Value, rng, False), 1).Interior.ColorIndex
                        End If
                    End If
                End If
    Next i
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *