excel - 通过比较两个不同的关键字来填充颜色

Question

我想通过比较具有不同单元格文本的两列来填充整行的颜色。如下图所示，如果我在第 1 列“PAID”和第 2 列“DONE”输入。我想要整行填充绿色。

我的代码是:

Dim itm As Range
Dim rw As Long
With Sheets("PO 2019_Vendor")
rw = .Range("A" & .Rows.Count).End(xlUp).Row

If .Range("AG" & rw).Value = "PAID" And .Range("AJ" & rw).Value = "DONE" Then itm.EntireRow.Interior.Color = 4

If .Range("AG" & rw).Value = "PAID" And .Range("AJ" & rw).Value = "NOTRCV" Then itm.EntireRow.Interior.Color = 6

If .Range("AG" & rw).Value = "PENDING" And .Range("AJ" & rw).Value = "DONE" Then itm.EntireRow.Interior.Color = 28

End With
End Sub

希望您能理解我的解释并帮助我。预先感谢您。

Answer 1

如果该列是 AG 和 AJ，并且标题位于第一行:

Sub erf()
Dim itm As Range
Dim rw As Long

    With Sheets("PO 2019_Vendor")

        rw = .Range("AG" & .Rows.Count).End(xlUp).Row

        For i = 2 To rw 'only if your headers are in 1 row

        If .Range("AG" & i).Value = "PAID" And .Range("AJ" & i).Value = "DONE" Then Rows(i).Interior.Color = 4

        If .Range("AG" & i).Value = "PAID" And .Range("AJ" & i).Value = "NOTRCV" Then Rows(i).EntireRow.Interior.Color = 6

        If .Range("AG" & i).Value = "PENDING" And .Range("AJ" & i).Value = "DONE" Then Rows(i).EntireRow.Interior.Color = 28

        Next

    End With

End Sub