跨列匹配数字并同步单元格颜色的Excel实现方案求助

同工作表匹配（A3:A7 → C3:C7） #

以下VBA代码可遍历源区域，将目标区域内数字匹配的单元格同步为对应背景色：

Sub MatchColorInSameSheet()
    Dim sourceRange As Range, targetRange As Range
    Dim sourceCell As Range, targetCell As Range
    
    ' 定义源区域和目标区域
    Set sourceRange = ThisWorkbook.ActiveSheet.Range("A3:A7")
    Set targetRange = ThisWorkbook.ActiveSheet.Range("C3:C7")
    
    ' 遍历源区域每个单元格
    For Each sourceCell In sourceRange
        If Not IsEmpty(sourceCell.Value) Then
            ' 在目标区域查找匹配数字并同步颜色
            For Each targetCell In targetRange
                If targetCell.Value = sourceCell.Value Then
                    targetCell.Interior.Color = sourceCell.Interior.Color
                End If
            Next targetCell
        End If
    Next sourceCell
End Sub

跨工作表匹配（Sheet1的A3:A7 → Sheet2的C3:C7） #

完全支持跨工作表操作，仅需指定对应工作表即可，代码如下：

Sub MatchColorAcrossSheets()
    Dim sourceSheet As Worksheet, targetSheet As Worksheet
    Dim sourceRange As Range, targetRange As Range
    Dim sourceCell As Range, targetCell As Range
    
    ' 指定源工作表和目标工作表
    Set sourceSheet = ThisWorkbook.Worksheets("Sheet1")
    Set targetSheet = ThisWorkbook.Worksheets("Sheet2")
    
    ' 定义源区域和目标区域
    Set sourceRange = sourceSheet.Range("A3:A7")
    Set targetRange = targetSheet.Range("C3:C7")
    
    ' 遍历匹配并同步颜色
    For Each sourceCell In sourceRange
        If Not IsEmpty(sourceCell.Value) Then
            For Each targetCell In targetRange
                If targetCell.Value = sourceCell.Value Then
                    targetCell.Interior.Color = sourceCell.Interior.Color
                End If
            Next targetCell
        End If
    Next sourceCell
End Sub

使用步骤 #

1. 打开Excel，按Alt + F11打开VBA编辑器
2. 右键当前工作簿 → 插入 → 模块
3. 将对应代码粘贴到模块中
4. 运行宏即可完成颜色同步

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