比较两列与颜色

Question

我有三列AA，AB和AE，我必须将AA和AB列与AE列分别分别对AA和AB列进行比较。宏应首先检查AA和AB列，并找到哪个列的琥珀色颜色，并且应该比较该列(仅当琥珀色的列具有"高"为"高"(与相应的AE列的天数小于335天是否。

我尝试的代码下面只能比较两个列AB和AE。如果AB列的值为"高"，则将检查相应的AE列的天数少于335天。

Dim High As Range
Dim StartDate As Date
StartDate = Date
   With ThisWorkbook.Worksheets("TEMPLATE")
LRow = .Cells(.Rows.Count, "AB").End(xlUp).Row
For Each High In .Range("AB11:AB" & LRow)
    If High.Value = "High" Then
        If IsDate(.Range("AE" & High.Row)) = False Then
            .Range("AE" & High.Row).Interior.Color = vbRed
        Else
            If DateValue(.Range("AE" & High.Row)) < StartDate - 335 Then .Range("AE" & High.Row).Interior.Color = vbRed
        End If
    End If
Next High
End With

Answer 1

我认为您要做的是您要做的...如果不是，它至少应该让您了解从哪里开始：(

将If(someValue = False)带有其他语句是非常困惑的，请翻转If

Dim High As Range
Dim StartDate As Date
Dim amberColor As Boolean
StartDate = Date
With ThisWorkbook.Worksheets("TEMPLATE")
    LRow = .Cells(.Rows.Count, "AB").End(xlUp).Row
    For Each High In .Range("AB11:AB" & LRow)
        ' Assume cells aren't amber
        amberColor = False
        ' If AA is Amber
        If .Range("AA" & High.Row).Interior.Color = RGB(255, 192, 0) Then
            'Move high to AA
            Set High = .Range("AA" & High.Row)
            'Color is amber
            amberColor = True
        ' If AB is Amber
        ElseIf .Range("AB" & High.Row).Interior.Color = RGB(255, 192, 0) Then
            ' High is already in AB, don't move
            ' Color is amber
            amberColor = True
        End If
        ' If the cell was amber and the value is High
        If amberColor And High.Value = "High" Then
            ' If AE contains a date
            If IsDate(.Range("AE" & High.Row)) Then
                ' If AE is more than 335 days ago
                If DateValue(.Range("AE" & High.Row)) < StartDate - 335 Then
                    ' Make AE red
                    .Range("AE" & High.Row).Interior.Color = vbRed
                End If
            Else
                ' AE isn't a date, make it red
                .Range("AE" & High.Row).Interior.Color = vbRed
            End If
        End If
    Next High
End With