excel - 对于一列中的每个单元格，检查下一列中的多个匹配项，然后突出显示它们

Question

在 Excel 表格中，我在一列中有保险单编号，在下一列中使用相同的保单编号对它们中的每一个进行背书。然后是第三列，其中包含针对每个保单和背书的未偿金额。

我想要一个宏来检查下一列中的政策匹配，然后检查未结金额是否既非零又相等。

我目前正在处理此代码:

Sub Outstandingknockoff() 
Dim Cell As Range 
LR = Cells(Rows.Count, 1).End(xlUp).Row
For Each Cell In Range("D2:D" & LR)
Match = Application.Match(Cell, Range("e2:e" & LR), 0)
If Match <> 0 And Cell.Offset(0, 16) <> 0 And Cell.Offset(0, 16) = -Range("T" & Match + 1) Then
On Error Resume Next
Cell.EntireRow.Interior.ColorIndex = 6
Match.EntireRow.Interior.ColorIndex = 6
End If
Next Cell
End Sub

Answer 1

双循环可以工作。

Sub DoubleLoop3()
    Dim rng1 As Range, rng2 As Range
    Dim a As Range, c As Range
    Set rng1 = Columns("D:D").EntireColumn.SpecialCells(xlCellTypeConstants, 23)
    Set rng2 = Columns("E:E").EntireColumn.SpecialCells(xlCellTypeConstants, 23)
    Columns("D:F").Interior.Color = xlNone

    For Each c In rng1.Cells
        For Each a In rng2.Cells
            If c = a And a.Offset(, 1) <> 0 Then
                c.Interior.ColorIndex = 6
                a.Interior.ColorIndex = 6
                a.Offset(, 1).Interior.ColorIndex = 6
            End If
        Next a
    Next c


End Sub