excel - vba 重复/空单元格 - 突出显示

Question

我需要为重复项创建类似的宏（见下文）。用户将能够选择一个列符号，然后所选列中的所有重复项将用颜色突出显示。我不知道该怎么做。

下面是相同的想法，但单元格为空。

你能帮忙吗？谢谢！

Sub EmptyCells()

    Dim kol As String
    Dim ost As Long

    ost = Cells(Rows.Count, "A").End(xlUp).Row
    kol = InputBox("Enter column symbol: B, C...etc.", "Column symbol", "B")

    If kol = vbNullString Then Exit Sub
    If IsNumeric(kol) Then
        MsgBox "You entered number, please enter column symbol", _
                vbInformation, "ERROR"
        Exit Sub
    End If
    If ost < 5 Then Exit Sub

    Range("A5:E" & ost).Interior.Color = xlNone

    Range(Cells(5, kol), Cells(ost, kol)).SpecialCells(xlCellTypeBlanks).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

End Sub

标签： excelvbacolorsduplicateshighlight

Answer 1

请根据您程序中的注释进行以下更改。更改后它对我有用。假设我已经清除了工作表上的现有格式，这将是第一个格式条件。

Sub DuplicateCells() ' changed sub name

    Dim kol As String
    Dim ost As Long

    ost = Cells(Rows.Count, "A").End(xlUp).Row
    kol = InputBox("Enter column symbol: B, C...etc.", "Column symbol", "B")

    If kol = vbNullString Then Exit Sub
    If IsNumeric(kol) Then
        MsgBox "You entered number, please enter column symbol", _
                vbInformation, "ERROR"
        Exit Sub
    End If
    If ost < 5 Then Exit Sub

    Range("A5:E" & ost).Interior.Color = xlNone

    Range(Cells(5, kol), Cells(ost, kol)).Select  ' Remove SpecialCells(xlCellTypeBlanks)
    Selection.FormatConditions.AddUniqueValues   'Add this line
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority      'Add this line
    Selection.FormatConditions(1).DupeUnique = xlDuplicate   'Add this line

    With Selection.FormatConditions(1).Interior   '    add FormatConditions(1)
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

显示了我的样本数据的结果。