Range.DisplayFormat method and 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