vba - Range.DisplayFormat 方法和 VBA Excel 2007

Question

我想检查我的列中是否有任何重复项，我手动设置了条件格式，然后进行了以下测试:

            If cell2.DisplayFormat.Interior.Color <> RGB(255, 199, 206) Then

                Label8.Caption = cell2.Offset(, 2).Text
                Label9.Caption = cell2.Offset(, 3).Text
                Label10.Caption = cell2.Offset(, 4).Text
                Label12.Caption = cell2.Offset(, 5).Text
                Label13.Caption = cell2.Offset(, 6).Text
                Label28.Caption = cell2.Offset(, 7).Text
                Label30.Caption = cell2.Offset(, 8).Text
                CommandButton2.Enabled = True

            Else
                cell2.Value = ""
                MsgBox "Votre bac existe déjà", vbExclamation, "Bac double"
                Me.TextBox1.Value = ""
                Me.TextBox1.SetFocus

            End If

问题是我用的是Excel 2007，这个不支持 Range.DisplayFormat方法，所以我需要的是以下之一:

Range.DisplayFormat 的替代方案方法。
或

另一种在不使用条件格式的情况下测试重复项的方法。

谢谢你!

Answer 1

您可以使用内置的条件格式功能

任何一个

1) 选择要突出显示的区域，然后转到Home > Conditional Formatting > Highlight cell rules > Duplicate Values并选择您的格式

或者

2)使用类似于以下的代码:

Sub findDupsInMyCol()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rngSelection As Range
    Dim lastRow As Long

    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 'find last used row number
    Set rngSelection = ws.Range("A1:A" & lastRow) 'set target range
    ws.Activate
    FormatDups rngSelection 

End Sub


Sub FormatDups(ByRef rngSelected As Range)

    rngSelected.FormatConditions.AddUniqueValues
    rngSelected.FormatConditions(rngSelected.FormatConditions.Count).SetFirstPriority
    With rngSelected.FormatConditions(1)
        .DupeUnique = xlDuplicate
        .Font.Color = -16383844
        .Interior.PatternColorIndex = xlAutomatic
        .Interior.Color = 13551615
        .StopIfTrue = False
    End With

End Sub