Excel VBA批量为多工作表设置XLOOKUP匹配类条件格式

核心需求 #

• 覆盖范围：当前工作簿内31个结构、数据规则完全一致的工作表，全部应用统一条件格式
• 生效区域：每个工作表的B4:B71、D4:D71、F4:F71、H4:H71、J4:J71、L4:L71、N4:N71、P4:P71、R4:R71
• 规则要求：上述区域存储姓名内容，若单元格值可在同表U13:U1146区域匹配到对应值，单元格自动填充黄色；参考H4单元格的判断逻辑，原示例公式为=$H$4=XLOOKUP($H$4;$U$13:$U$1146;$V$13:$V$1146)

注意：原示例公式用了全绝对引用$H$4，会导致全区域所有单元格都以H4的值做判断，不符合逐单元格校验的需求，代码中已修正引用写法。如果你的Excel默认用分号作为公式参数分隔符，可将代码中公式里的逗号替换为分号。

实现代码 #

按Alt+F11打开VBA编辑器，插入标准模块，粘贴以下代码后按F5运行即可：

Sub 批量设置条件格式()
    Dim ws As Worksheet
    Dim targetRng As Range
    Dim judgeFormula As String
    
    ' 关闭屏幕更新，提升运行速度
    Application.ScreenUpdating = False
    
    ' 遍历当前工作簿所有工作表
    For Each ws In ThisWorkbook.Worksheets
        ' 定义需要应用规则的区域
        Set targetRng = ws.Range("B4:B71,D4:D71,F4:F71,H4:H71,J4:J71,L4:L71,N4:N71,P4:P71,R4:R71")
        ' 先清除区域原有条件格式，避免重复生成冗余规则
        targetRng.FormatConditions.Delete
        
        ' 条件判断公式：逐单元格匹配U列名单，匹配成功返回真
        ' 若使用的Excel版本不支持XLOOKUP，可替换为下面兼容所有版本的公式
        ' judgeFormula = "=NOT(ISERROR(MATCH(B4,$U$13:$U$1146,0)))"
        judgeFormula = "=B4=XLOOKUP(B4,$U$13:$U$1146,$V$13:$V$1146)"
        
        ' 添加条件格式，设置匹配成功填充黄色
        With targetRng.FormatConditions.Add(Type:=xlExpression, Formula1:=judgeFormula)
            .Interior.Color = vbYellow
        End With
NextLoop:
    Next ws
    
    ' 恢复屏幕更新
    Application.ScreenUpdating = True
    MsgBox "全部工作表条件格式配置完成", vbInformation
End Sub

使用说明 #

• 运行代码前务必备份原工作簿，避免误操作导致格式或数据异常
• 如果不需要给所有工作表配置规则，可在遍历逻辑中加判断跳过指定工作表，例如要跳过名为"参数表""汇总表"的工作表，可在For Each ws In ThisWorkbook.Worksheets下添加代码：

  If ws.Name = "参数表" Or ws.Name = "汇总表" Then GoTo NextLoop
  

• 配置完成后可随机抽查几个单元格的条件格式规则，确认公式引用符合预期，若出现匹配错误优先检查公式参数分隔符是否和本地Excel设置一致

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