Flip Rows and Columns and Conditional Formatting - Please Help

See the attached file.

I used the macro below to get the result.

Sub CONVERTROWSTOCOL_Oeldere_revisted()

Dim rsht1 As Long, rsht2 As Long, i As Long, col As Long, wsTest As Worksheet

'check if sheet "ouput" already exist

Const strSheetName As String = "Output"
 
Set wsTest = Nothing
On Error Resume Next
Set wsTest = ActiveWorkbook.Worksheets(strSheetName)
On Error GoTo 0
 
If wsTest Is Nothing Then
    Worksheets.Add.Name = strSheetName
End If

   
   With Sheets("Output")
    .UsedRange.ClearContents
    .Range("A1:D1").Value = Array("Work", "Choise", "Name", "value")

    End With
    
    
    rsht1 = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Row
    rsht2 = Sheets("Output").Range("A" & Rows.Count).End(xlUp).Row
    col = 3
    
    For i = 4 To rsht1
        
        Do While Sheets("sheet2").Cells(3, col).Value <> ""
            rsht2 = rsht2 + 1
            Sheets("Output").Range("A" & rsht2).Value = Sheets("sheet2").Range("A" & i).Value
            Sheets("Output").Range("B" & rsht2).Value = Sheets("sheet2").Range("B" & i).Value
            Sheets("Output").Range("C" & rsht2).Value = Sheets("sheet2").Cells(3, col).Value
            Sheets("Output").Range("D" & rsht2).Value = Sheets("sheet2").Cells(i, col).Value
            
     
            col = col + 1
        Loop
        col = 3
    Next
  With Sheets("Output")
'   .Range("D2:D" & .Rows.Count).SpecialCells(4).EntireRow.Delete
   Columns("A:Z").EntireColumn.AutoFit
    End With
End Sub

Select your range of data, choose Condtional Format -> Formula

Red Fill
=IF(AND(C4>0,C4<DATE(YEAR(TODAY()),MONTH(TODAY())-24,DAY(TODAY()))),TRUE,FALSE)

Blue Fill
=IF($A$1:$CG$20>=DATE(YEAR(TODAY()),MONTH(TODAY())-24,DAY(TODAY())),TRUE,FALSE)

@ daffodil11

CF formulas don't need the if statement
The result of a CF formula is always TRUE or FALSE

=AND(C4>0,C4<DATE(YEAR(TODAY()),MONTH(TODAY())-24,DAY(TODAY())))

CF formula for red cells:

=AND(C4<>"",C4<TODAY()-730)

for blue cells:

=AND(C4<>"",C4>=TODAY()-730)

Duplicate threads merged.

When you joined the forum recently you agreed to abide by the Forum Rules, but in haste I fear you might not have actually read them. Please stop and take a moment to read them now. We all follow these rules for the benefit of all, as must you. Thanks.

(link above in the menu bar)

I tried a bajillion times to do it without the IF statement and it kept putting silly quotes around my formula.

$A$1:$CG$20>=DATE(YEAR(TODAY()),MONTH(TODAY())-24,DAY(TODAY())

What am I doing wrong? If I delete them, they just reappaear!

CF Quotes.png

=and(c4>0,c4>=date(year(today()),month(today())-24,day(today())))