循环设置条件格式时忽略空单元格问题（xlLess等运算符）

问题原因 #

你当前使用的xlCellValue类型条件格式会把空单元格（包括公式返回空值的单元格）判定为小于100，因此错误触发标红规则。

解决方案 #

改用公式型条件格式，通过Excel公式精准控制判断逻辑，既排除空单元格，又保留原有格式（前景色、边框不受影响）。

代码实现（合并规则，更高效）

' 清除目标区域已有条件格式，避免规则冲突
Anomalies_sheet.Range(Cells(FirstLine, K), Cells(I - 1, K)).FormatConditions.Delete

' 添加单个公式型条件格式，只对非空且数值超范围的单元格标红
With Anomalies_sheet.Range(Cells(FirstLine, K), Cells(I - 1, K)).FormatConditions.Add(Type:=xlExpression, Formula1:= _
    "=AND(NOT(ISBLANK(" & Cells(FirstLine, K).Address(False, False) & ")),OR(" & Cells(FirstLine, K).Address(False, False) & ">180," & Cells(FirstLine, K).Address(False, False) & "<100))")
    .Font.Color = vbRed ' 仅设置字体标红，不修改原有格式
End With

代码说明

• xlExpression：指定条件格式基于自定义公式判断
• NOT(ISBLANK(...))：排除空单元格（含公式返回空值的情况）
• OR(...)：判断数值是否大于180或小于100
• Address(False, False)：生成相对单元格地址，确保规则应用到区域内每个单元格时自动适配
• 仅修改字体颜色，保留原有前景色、边框等格式，符合需求

可选：分开设置两个规则

如果需要分开定义"小于100"和"大于180"的规则，可使用以下代码：

Anomalies_sheet.Range(Cells(FirstLine, K), Cells(I - 1, K)).FormatConditions.Delete

' 规则1：非空且数值小于100
With Anomalies_sheet.Range(Cells(FirstLine, K), Cells(I - 1, K)).FormatConditions.Add(Type:=xlExpression, Formula1:= _
    "=AND(NOT(ISBLANK(" & Cells(FirstLine, K).Address(False, False) & "))," & Cells(FirstLine, K).Address(False, False) & "<100)")
    .Font.Color = vbRed
End With

' 规则2：非空且数值大于180
With Anomalies_sheet.Range(Cells(FirstLine, K), Cells(I - 1, K)).FormatConditions.Add(Type:=xlExpression, Formula1:= _
    "=AND(NOT(ISBLANK(" & Cells(FirstLine, K).Address(False, False) & "))," & Cells(FirstLine, K).Address(False, False) & ">180)")
    .Font.Color = vbRed
End With

内容的提问来源于stack exchange，提问作者Malou