CODEWITHSUNDEEP
excelvba
Deepak L
Deepak L

Reputation: 31

Comparing two columns with the colour

I have three columns AA, AB and AE where I have to compare the AA and AB column with the AE column for all the rows individually. Macro should first check AA and AB column and find which of the column have the Amber colour and it should compare that column (only if that Amber column have value as "High") with the corresponding AE column is having the days less than 335 days or not.

Below code I tried which will compare only two columns AB and AE. If AB column have the value as "High" it will check the corresponding AE column is having the days less than 335 days or not.

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

Upvotes: 0

Views: 50

Answers (1)

seadoggie01
seadoggie01

Reputation: 530

I think is what you're trying to do... if not, it should at least give you an idea of where to start :)

It's quite confusing to have If(someValue = False) with an else statement, flip the If instead

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

Upvotes: 1

Related Questions