如何基于其他工作表的Pass/Fail结果实现单元格及整行条件格式着色？

前提说明 #

假设你的文件包含两张工作表：

• DataSheet：存放X、Y列的Z标识（如Z1、Z2）
• ResultSheet：A列是Z标识，B列对应Pass/Fail结果

如果你的表名或列位置不同，可在代码中对应修改。

实现步骤 #

1. 打开VBA编辑器 #

按Alt+F11打开VBA编辑器，右键左侧「工程资源管理器」→「插入」→「模块」，将以下代码粘贴到模块窗口中：

Sub ZTagColorFormat()
    Dim dataWs As Worksheet, resultWs As Worksheet
    Dim resultDict As Object
    Dim rowNum As Long, colNum As Integer
    Dim passCount As Integer, failCount As Integer
    Dim cell As Range
    
    ' 绑定工作表对象（根据实际表名修改）
    Set dataWs = ThisWorkbook.Worksheets("DataSheet")
    Set resultWs = ThisWorkbook.Worksheets("ResultSheet")
    ' 创建字典存储标识与结果的映射，提升匹配效率
    Set resultDict = CreateObject("Scripting.Dictionary")
    
    ' 加载结果对照表到字典
    For Each cell In resultWs.Range("A2:A" & resultWs.Cells(resultWs.Rows.Count, "A").End(xlUp).Row)
        If Not resultDict.Exists(cell.Value) Then
            resultDict(cell.Value) = cell.Offset(0, 1).Value
        End If
    Next cell
    
    ' 遍历数据工作表的每一行
    For rowNum = 2 To dataWs.Cells(dataWs.Rows.Count, "A").End(xlUp).Row
        passCount = 0
        failCount = 0
        
        ' 第一步：统计当前行的Pass/Fail数量
        For colNum = 1 To 2 ' 1=X列(A), 2=Y列(B)，根据实际列号修改
            Set cell = dataWs.Cells(rowNum, colNum)
            If resultDict.Exists(cell.Value) Then
                Select Case resultDict(cell.Value)
                    Case "Pass": passCount = passCount + 1
                    Case "Fail": failCount = failCount + 1
                End Select
            End If
        Next colNum
        
        ' 第二步：设置整行背景色
        Select Case True
            Case passCount = 2
                dataWs.Rows(rowNum).Interior.ColorIndex = 4 ' 绿色
            Case failCount = 2
                dataWs.Rows(rowNum).Interior.ColorIndex = 3 ' 红色
            Case passCount = 1 And failCount = 1
                dataWs.Rows(rowNum).Interior.ColorIndex = 6 ' 黄色
            Case Else
                dataWs.Rows(rowNum).Interior.ColorIndex = xlColorIndexNone ' 无色
        End Select
        
        ' 第三步：设置单个单元格颜色（覆盖对应位置的整行颜色）
        For colNum = 1 To 2
            Set cell = dataWs.Cells(rowNum, colNum)
            If resultDict.Exists(cell.Value) Then
                Select Case resultDict(cell.Value)
                    Case "Pass": cell.Interior.ColorIndex = 4
                    Case "Fail": cell.Interior.ColorIndex = 3
                End Select
            Else
                cell.Interior.ColorIndex = xlColorIndexNone
            End If
        Next colNum
    Next rowNum
    
    ' 释放对象资源
    Set resultDict = Nothing
    Set dataWs = Nothing
    Set resultWs = Nothing
End Sub

2. 代码适配修改 #

• 工作表名称：如果你的数据工作表不是DataSheet，结果表不是ResultSheet，修改代码中ThisWorkbook.Worksheets("XXX")的引号内容
• 列位置：如果X、Y列不是A、B列，将colNum = 1 To 2改为对应列号（比如X是C列、Y是D列，改为3 To 4）
• 颜色自定义：ColorIndex是Excel内置颜色索引，也可以用RGB值更直观，比如将cell.Interior.ColorIndex = 4改为cell.Interior.Color = RGB(0, 255, 0)（纯绿）

3. 运行宏 #

回到Excel界面，按Alt+F8，选择ZTagColorFormat，点击「执行」即可完成着色。你也可以插入表单按钮，将宏绑定到按钮上，实现一键运行。

原理说明 #

1. 字典映射提速：用Scripting.Dictionary将ResultSheet的Z标识和结果存储为键值对，避免循环使用VLOOKUP/MATCH的低效查询，大幅提升处理速度
2. 分阶段着色逻辑：
   • 先统计每行的Pass/Fail数量，以此判断整行的着色规则
   • 再设置单个单元格的颜色，确保单个标识的状态能在整行底色上清晰显示
3. 边界处理：对未匹配到结果的Z标识，单元格和整行均设为无色，避免错误着色

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