如何在多个工作表上运行3个条件格式化循环

Question

我试图有条件地格式化4张范围不同的工作表，而不用选择

我试图清理我的非常疯狂的初学者代码，并加快进程，但循环不起作用。工作表2和3的范围内的所有空单元格都应填充"T“。第4和5页范围内的空单元格应为"p“。表格2-4上数据的所有单元格格式相同:粗体字体、中心对齐、帧、有条件替换文本和字体&字体颜色取决于单元格文本。

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

将循环替换为这个循环--循环的作用与With语句不同--您仍然必须显式地引用cell.Text/cell.Value --除非您希望在With语句中嵌入With语句--这是绝对可以的--但即使这样，也必须是.Text和.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

Answer 2

我发现，如果我使用Select和ws名称来代替我，并添加“每个ws.在每个选择案例之前，它的工作和运行速度都非常快。也许不是最优雅的，而是有效的。”

Sub comfor()

Dim daily As Worksheet, mon As Worksheet, per As Worksheet, surf As Worksheet
Dim ws As Worksheet, cell As Range


Set daily = Sheets("Daily")
Set per = Sheets("Personnel")
Set surf = Sheets("Testing")
Set mon = Sheets("Monthly")

For Each ws In ActiveWorkbook.Sheets
 Select Case ws.Name
    Case "Daily", "Monthly"
        For Each cell In ws.Range(("A6"),_ 
ws.Range("A6").SpecialCells(xlLastCell)).Cells
                If cell.Text = "" Then
                   cell.Value = "T"
                    cell.Font.Color = vbRed
                   cell.Value = "T"
                   cell.Font.Name = "Wingdings 2"
                   End If
            Next
    End Select
 Next

For Each ws In ActiveWorkbook.Sheets
 Select Case ws.Name
    Case "Personnel", "Testing"
    For Each cell In ws.Range(("A6"), ws.Range("A6").SpecialCells(xlLastCell)).Cells
             If cell.Text = "" Then
                cell.Value = "p"
                    cell.Font.Color = RGB(255, 192, 0)
                    cell.Value = "p"
                    cell.Font.Name = "Wingdings 3"
                    End If
        Next

    End Select
Next

For Each ws In ActiveWorkbook.Sheets
 Select Case ws.Name
    Case "Daily", "Monthly", "Personnel", "Testing"
        For Each cell In ws.Range(ws.Range("A6"),_ 
ws.Range("A6").SpecialCells(xlLastCell)).Cells
           With cell
                .HorizontalAlignment = xlCenter
           End With

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

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

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

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

                End If

            Next
    End Select
 Next

End Sub