VBA实现Sheet1单元格值在Sheet2列的存在性校验与单元格着色

原代码存在的问题 #

1. 内层循环对比错误：你在对比时用了WS.Cells(i, 2)，应该是WS.Cells(j, 2)，否则只会和Master List的同一行单元格对比，无法遍历整个列表。
2. Selection对象滥用：直接操作Selection会导致不可预期的错误，应该直接指定目标单元格WS1.Cells(i, 2)的格式属性。
3. 逻辑与语法错误：
   • VBA中Else If需写成ElseIf（无空格）；
   • 两种分支设置了相同颜色，没有区分“存在（绿色）”和“不存在（红色）”；
   • 内层循环的to应为大写To。

修正后的双重循环方案（适合新手理解） #

这种方案通过两层循环逐一比对，逻辑直观：

Dim WS As Worksheet
Set WS = Sheets("Master List")
Dim WS1 As Worksheet
Set WS1 = Sheets("Campaign")

Dim i As Integer, j As Integer
Dim isFound As Boolean ' 标记当前单元格是否找到匹配

For i = 2 To 100
    isFound = False ' 每次循环重置匹配标记
    ' 遍历Master List的B列所有目标单元格
    For j = 2 To 100
        If WS1.Cells(i, 2).Value = WS.Cells(j, 2).Value Then
            isFound = True
            Exit For ' 找到匹配后直接跳出内层循环，提升效率
        End If
    Next j
    
    ' 根据匹配结果设置单元格颜色
    With WS1.Cells(i, 2).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        If isFound Then
            .Color = 5287936 ' 绿色（匹配成功）
        Else
            .Color = 255 ' 红色（匹配失败）
        End If
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
Next i

高效方案：使用Match函数（避免双重循环） #

当数据量较大时，双重循环效率较低，用Application.Match函数可以直接查找匹配项，大幅提升速度：

Dim WS As Worksheet
Set WS = Sheets("Master List")
Dim WS1 As Worksheet
Set WS1 = Sheets("Campaign")

Dim i As Integer
Dim masterRange As Range
Set masterRange = WS.Range("B2:B100") ' Master List的目标列范围

For i = 2 To 100
    With WS1.Cells(i, 2).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        ' Match函数精确查找，找不到返回错误，用IsError判断
        If Not IsError(Application.Match(WS1.Cells(i, 2).Value, masterRange, 0)) Then
            .Color = 5287936 ' 绿色（匹配成功）
        Else
            .Color = 255 ' 红色（匹配失败）
        End If
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
Next i

关键说明 #

• Match函数的第三个参数0表示精确匹配，确保完全一致的字符串才会被识别；
• 使用With语句可以简化代码，避免重复引用单元格对象；
• 如果Master List的目标列不是B列，只需修改masterRange的列号即可。

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