How to run 3 loops for conditional formatting over multiple worksheets

Question

I'm trying to conditionally format 4 worksheets with varying ranges without using Select

I'm trying to clean up my very crazy beginner code and speed up the process, but the loops are not working. All of the empty cells in the ranges on Worksheets 2 & 3 should be filled with "T". The empty cells in ranges on Sheets 4 & 5 should be "p". All of the cells with data on sheets 2-4 are formatted the same: bold font, center alignment, frame, conditionally replace text and font & font color depending on cell text.

Sub comfor()

Dim ws As Worksheet, cell As Range

For Each ws In ActiveWorkbook.Sheets
    For i = 2 To 3
        With Sheets(i)
            For Each cell In ws.Range(ws.Range("A6"),_ ws.Range("A6").SpecialCells(xlLastCell)).Cells
                   If Text = "" Then
                   Value = "T"
                End If
            Next
         End With
      Next

    For i = 4 To 5
        With Sheets(i)
            For Each cell In ws.Range(ws.Range("A6"),_ ws.Range("A6").SpecialCells(xlLastCell)).Cells
                 If Text = "Not Recorded" Then
                    Value = "p"
                End If
            Next
        End With
    Next
    For i = 2 To 5
        With Sheets(i)
            For Each cell In ws.Range(ws.Range("A6"),_ ws.Range("A6").SpecialCells(xlLastCell)).Cells
               With cell
                    .HorizontalAlignment = xlCenter
                   .Font.Bold = True
               End With

               With cell
                   .Borders(xlEdgeLeft).Weight = xlMedium
                   .Borders(xlEdgeTop).Weight = xlMedium
                   .Borders(xlEdgeBottom).Weight = xlMedium
                   .Borders(xlEdgeRight).Weight = xlMedium
               End With

               With cell
                    If .Text = "Incomplete" Then
                       .Font.Color = vbRed
                       .Value = "T"
                       .Font.Name = "Wingdings 2"

                    ElseIf .Text = "Not Applicable" Then
                        .Name = "Webdings"
                        .Value = "x"
                        .Font.Color = RGB(255, 192, 0)

                    ElseIf .Text = "Complete" Then
                        .Font.Color = 5287936
                        .Value = "R"
                        .Font.Name = "Wingdings 2"

                    ElseIf .Text = "Not Recorded" Then
                        .Font.Color = RGB(129, 222, 225)
                        .Value = "p"
                        .Font.Name = "Wingdings"

                    End If
                End With
            Next
        End With
    Next
 Next

End Sub

Answer 1

Replace your loops with this instead - a loop does not act the same as a With statement - you still have to explicitly refer to cell.Text/cell.Value - UNLESS you want to embed a With statement inside your loop - which you absolutely could - but even then, that would need to be .Text and .Value.

For i = 2 To 3
        With Sheets(i)
            For Each cell In ws.Range(ws.Range("A6"), ws.Range("A6").SpecialCells(xlLastCell)).Cells
                   If cell.Text = "" Then
                   cell.Value = "T"
                End If
            Next
         End With
      Next

    For i = 4 To 5
        With Sheets(i)
            For Each cell In ws.Range(ws.Range("A6"), ws.Range("A6").SpecialCells(xlLastCell)).Cells
                 If cell.Text = "Not Recorded" Then
                    cell.Value = "p"
                End If
            Next
        End With
    Next