Excel 循环、计算和着色宏跳过单元格

Question

在第 9 行中，我有一组标准基准数值，但并非所有单元格都已填充。第 10 到 29 行是一组测试结果，但有时不会填充所有行和/或不会填充行内的所有单元格。附上示例图片。
我将以下代码放在一起循环遍历范围并突出显示比基准大 5-10%、10-15% 或 15+% 或 5-10%、10-15% 或 15+% 的测试结果低于基准。
它似乎适用于某些结果，但会跳过其他结果。任何人都可以看到问题是什么？
谢谢

Sub HighlightStats()
   Dim r As Long, c As Long
   
   With ThisWorkbook.Sheets("Summary")
   
    For r = 10 To 29
      For c = 4 To 63
         If Cells(9, c) <> "" And Cells(r, c) >= Cells(9, c) * 1.05 And Cells(r, c) < Cells(9, c) * 1.1 And Cells(r, c) <> "" Then Cells(r, c).Interior.Color = RGB(211, 222, 241)
      Next c
   Next r
   
   For r = 10 To 29
      For c = 4 To 63
         If Cells(9, c) <> "" And Cells(r, c) >= Cells(9, c) * 1.1 And Cells(r, c) < Cells(9, c) * 1.15 And Cells(r, c) <> "" Then Cells(r, c).Interior.Color = RGB(180, 198, 231)
      Next c
   Next r
   
    For r = 10 To 29
      For c = 4 To 63
         If Cells(9, c) <> "" And Cells(r, c) >= Cells(9, c) * 1.5 And Cells(r, c) <> "" Then Cells(r, c).Interior.Color = RGB(110, 145, 208)
      Next c
   Next r
   
    For r = 10 To 29
      For c = 4 To 63
         If Cells(9, c) <> "" And Cells(r, c) <= Cells(9, c) * 0.95 And Cells(r, c) > Cells(9, c) * 0.9 And Cells(r, c) <> "" Then Cells(r, c).Interior.Color = RGB(237, 219, 236)
      Next c
   Next r
   
   For r = 10 To 29
      For c = 4 To 63
         If Cells(9, c) <> "" And Cells(r, c) <= Cells(9, c) * 0.9 And Cells(r, c) > Cells(9, c) * 0.85 And Cells(r, c) <> "" Then Cells(r, c).Interior.Color = RGB(223, 189, 221)
      Next c
   Next r
   
   For r = 10 To 29
      For c = 4 To 63
         If Cells(9, c) <> "" And Cells(r, c) <= Cells(9, c) * 0.85 And Cells(r, c) <> "" Then Cells(r, c).Interior.Color = RGB(207, 157, 205)
      Next c
   Next r

    End With

End Sub

Answer 1

我已将所有循环合二为一并修复了您的参数(1.15 应该是 1.5):

Sub HighlightStats()

    Dim r As Long, c As Long
    Dim sngCell As Single

    With ThisWorkbook.Sheets("Summary")
        For r = 10 To 29
            For c = 4 To 63
                sngCell = Cells(9, c).Value
                If sngCell <> "" And Cells(r, c) >= Cells(9, c) * 1.15 And Cells(r, c) <> "" Then Cells(r, c).Interior.Color = RGB(110, 145, 208)
                ElseIf sngCell <> "" And Cells(r, c) >= sngCell * 1.1 And Cells(r, c) < sngCell * 1.15 And Cells(r, c) <> "" Then Cells(r, c).Interior.Color = RGB(180, 198, 231)
                ElseIf sngCell <> "" And Cells(r, c) >= sngCell * 1.05 And Cells(r, c) < sngCell * 1.1 And Cells(r, c) <> "" Then Cells(r, c).Interior.Color = RGB(211, 222, 241)
                ElseIf sngCell <> "" And Cells(r, c) <= sngCell * 0.95 And Cells(r, c) > sngCell * 0.9 And Cells(r, c) <> "" Then Cells(r, c).Interior.Color = RGB(237, 219, 236)
                ElseIf sngCell <> "" And Cells(r, c) <= sngCell * 0.9 And Cells(r, c) > sngCell * 0.85 And Cells(r, c) <> "" Then Cells(r, c).Interior.Color = RGB(223, 189, 221)
                ElseIf sngCell <> "" And Cells(r, c) <= sngCell * 0.85 And Cells(r, c) <> "" Then Cells(r, c).Interior.Color = RGB(207, 157, 205)
                End if
             Next c
        Next r
    End With
 
End Sub