vba - 突出显示重复金额的重复客户。在excel中

Question

我正在尝试使用具有重复金额的 VBA 客户端在 Excel 中突出显示和标记。例如:

Client   Amount
123      $5
123      $6
456      $5
456      $9
456      $9

我当前的代码将这样标记示例:

Client   Amount    Check
123      $5        Duplicate
123      $6        OK
456      $5        Duplicate
456      $9        Duplicate
456      $9        Duplicate

实际上什么时候

Client   Amount 
123      $5
456      $5

应标记为 OK，因为它不是同一客户端上的重复金额。

我的代码如下。

'Checks For Duplicates
k = 2
Set AccClm = Range("A2:A" & rowz) ' area to check '
Set AmtClm = Range("C2:C" & rowz)
iWarnColor = xlThemeColorAccent2

Do While ws.Cells(k, 1) <> ""
Acc = ws.Cells(k, 1)
Amt = ws.Cells(k, 3)
    If (WorksheetFunction.CountIf(AccClm, Acc) >= 2) And (WorksheetFunction.CountIf(AmtClm, Amt) >= 2) Then
                ws.Rows(k).Interior.ColorIndex = iWarnColor
                ws.Cells(k, 4) = "Duplicate"
            Else
                ws.Rows(k).Interior.Pattern = xlNone
    End If
    k = k + 1
Loop

我被卡住了，任何想法都将不胜感激。

Answer 1

使用 COUNTIFS 而不是 COUNTIF

        'Checks For Duplicates
    k = 2
    Set AccClm = Range("A2:A" & rowz) ' area to check '
    Set AmtClm = Range("C2:C" & rowz)
    iWarnColor = xlThemeColorAccent2

    Do While ws.Cells(k, 1) <> ""
    Acc = ws.Cells(k, 1)
    Amt = ws.Cells(k, 3)
        If WorksheetFunction.CountIfs(AccClm, Acc, AmtClm, Amt) >= 2 Then
                    ws.Rows(k).Interior.ColorIndex = iWarnColor
                    ws.Cells(k, 4) = "Duplicate"
                Else
                    ws.Rows(k).Interior.Pattern = xlNone
        End If
        k = k + 1
    Loop