Excel VBA中使用InStr循环匹配单元格内容并着色问题排查

我看了你的代码，发现几个关键问题导致它无法正常工作，我会帮你修正并解释清楚每个问题：

原代码的问题点 #

• 数组元素没有.Value属性：你把nameone和nametwo赋值为单元格区域的值数组，所以cem和cel是单纯的文本值，不是Range对象，不能用.Value调用。
• InStr里的字符串错误：你写了InStr(cem.Value, "cel.Value")，这里的"cel.Value"是固定字符串，不是变量cel的值，根本不会匹配到任何内容。
• 错误设置了单元格内容而非颜色：你写的cem.Value = RGB(0,0,255)是把单元格内容改成颜色数值，而不是设置背景色，应该用.Interior.Color。
• 循环逻辑效率低且定位错误：直接遍历数组元素无法关联回Sheet1的单元格，没法设置颜色。

修正后的代码 #

Sub MatchAndColorNames()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim nameRange1 As Range, nameRange2 As Range
    Dim cell1 As Range, cell2 As Range
    
    ' 定义工作表对象，避免硬编码名称出错
    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Set ws2 = ThisWorkbook.Sheets("Sheet2")
    
    ' 动态获取列中最后一个非空单元格，避免固定行数的冗余循环
    Set nameRange1 = ws1.Range("L1:L" & ws1.Cells(ws1.Rows.Count, "L").End(xlUp).Row)
    Set nameRange2 = ws2.Range("M1:M" & ws2.Cells(ws2.Rows.Count, "M").End(xlUp).Row)
    
    ' 遍历Sheet1的每个姓名单元格
    For Each cell1 In nameRange1
        ' 先清除原有颜色（可选，根据需求保留或删除）
        cell1.Interior.ColorIndex = xlColorIndexNone
        
        ' 遍历Sheet2的姓名进行匹配
        For Each cell2 In nameRange2
            ' 检查Sheet1姓名是否包含Sheet2姓名，vbTextCompare实现不区分大小写匹配
            If InStr(1, cell1.Value, cell2.Value, vbTextCompare) > 0 Then
                ' 设置单元格背景为蓝色
                cell1.Interior.Color = RGB(0, 0, 255)
                ' 找到匹配后跳出内层循环，提升运行效率
                Exit For
            End If
        Next cell2
    Next cell1
End Sub

代码说明 #

• 使用工作表对象：用Set ws1明确指定工作表，比直接硬编码Sheets("Sheet1")更稳定，避免工作表名称修改后出错。
• 动态范围获取：自动定位列L和M的最后非空行，减少不必要的循环次数，适配数据量变化。
• 正确的匹配逻辑：InStr(1, cell1.Value, cell2.Value, vbTextCompare)实现了“Sheet1姓名包含Sheet2姓名部分内容”的匹配需求，去掉vbTextCompare可改为区分大小写匹配。
• 高效循环：找到匹配项后立即跳出内层循环，避免无意义的遍历。
• 正确设置颜色：用.Interior.Color而非修改单元格内容，准确设置背景色。

你可以直接复制这段代码替换原代码，运行后就能实现你想要的效果：当Sheet1的姓名包含Sheet2中任意一个姓名的部分内容时，将Sheet1对应单元格染成蓝色。

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