excel - 在范围内标记重复项，跳过空单元格

Question

我正在使用 VBA 在 J 列中标记重复项。我想用不同的颜色标记重复项。但是我也想跳过空单元格。标记重复项正在工作，但它没有跳过空单元格。空单元格也用颜色标记。

我当前的代码可能在哪里出现问题？

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

标签： excelvba