ApplyColorLogic

Sub ApplyColorLogic()
    Dim lastRow As Long
    Dim currentRow As Long

    ' Find the last row with data in column A
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row

    ' Loop through rows starting from 2 to the last row
    For currentRow = 2 To lastRow
        ' Check if C2 has no value
        If IsEmpty(Cells(currentRow, 3).Value) Then
            ' Move to the next row
            GoTo NextRow
        End If

        ' If C2 has a value and D2 has a value, turn both C2 and D2 green
        Cells(currentRow, 3).Resize(, 2).Interior.color = RGB(0, 204, 0)

        ' If C2 to K2 are all either green or white, turn A2 and B2 green
        If CheckAllGreenOrWhite(currentRow, 3, 11) Then
            Cells(currentRow, 1).Resize(, 2).Interior.color = RGB(0, 204, 0)
        End If

        ' If any cells in C2 to K2 are red, turn A2 and B2 red
        If CheckForColor(currentRow, 3, 11, RGB(255, 0, 0)) Then
            Cells(currentRow, 1).Resize(, 2).Interior.color = RGB(255, 0, 0)
        End If

        ' If any cells in C2 to K2 are orange (but none are red), turn A2 and B2 orange
        If CheckForColor(currentRow, 3, 11, RGB(255, 192, 0)) And _
           Not CheckForColor(currentRow, 3, 11, RGB(255, 0, 0)) Then
            Cells(currentRow, 1).Resize(, 2).Interior.color = RGB(255, 192, 0)
        End If

        ' If any cells in C2 to K2 are yellow (but none are orange or red), turn A2 and B2 yellow
        If CheckForColor(currentRow, 3, 11, RGB(255, 255, 0)) And _
           Not CheckForColor(currentRow, 3, 11, RGB(255, 192, 0)) And _
           Not CheckForColor(currentRow, 3, 11, RGB(255, 0, 0)) Then
            Cells(currentRow, 1).Resize(, 2).Interior.color = RGB(255, 255, 0)
        End If

NextRow:
    Next currentRow
End Sub

Function CheckAllGreenOrWhite(rowNum As Long, startCol As Long, endCol As Long) As Boolean
    Dim col As Long
    For col = startCol To endCol
        If Cells(rowNum, col).DisplayFormat.Interior.color <> RGB(0, 204, 0) And _
           Cells(rowNum, col).DisplayFormat.Interior.color <> RGB(255, 255, 255) Then
            CheckAllGreenOrWhite = False
            Exit Function
        End If
    Next col
    CheckAllGreenOrWhite = True
End Function

Function CheckForColor(rowNum As Long, startCol As Long, endCol As Long, color As Long) As Boolean
    Dim col As Long
    For col = startCol To endCol
        If Cells(rowNum, col).DisplayFormat.Interior.color = color Then
            CheckForColor = True
            Exit Function
        End If
    Next col
    CheckForColor = False
End Function