如何修改Excel VBA代码实现按钮触发双区域重复值检测

原代码运行错误的核心原因 #

• 未显式绑定目标工作表，操作时容易因当前激活的工作表与实际数据所在表不一致，触发对象引用错误
• 直接将拼接后的非连续Union区域传入CountIf工作表函数，多数桌面版Excel中会直接报参数兼容性错误——VBA调用该函数时，不支持将多区域非连续Range对象作为计数范围参数，无法正常完成跨两列的全局重复值统计
• 批量清除单元格原有高亮格式时未做异常兜底，若工作表开启保护、部分单元格处于锁定状态，会直接中断代码运行

具体修改逻辑 #

• 所有单元格操作显式锚定业务数据所在的工作表，从根源上避免跨表错位
• 绕开CountIf对非连续区域的支持限制：分别对C列目标范围、O列目标范围做独立的重复值计数，两个计数结果相加即为单元格值在全目标区域的总出现次数
• 清除原有格式的步骤增加错误捕获，避免工作表保护场景下的运行中断

修正后可直接运行的完整代码 #

Sub CheckDuplicateVALUE()
    ' 绑定存储业务数据的工作表，需将"Sheet1"替换为实际对应的工作表名称
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    Dim Cell As Range
    Dim Source As Range
    ' 拼接两个需要处理的非连续目标区域
    Set Source = Union(ws.Range("C4:C200"), ws.Range("O4:O200"))
    
    ' 清除原有高亮格式，增加异常捕获跳过无权限修改的单元格
    On Error Resume Next
    Source.Interior.Color = RGB(255, 255, 255)
    On Error GoTo 0
    
    ' 遍历目标区域内的所有单元格
    For Each Cell In Source
        ' 分别统计当前单元格值在C列、O列目标范围的出现次数
        Dim cntC As Long, cntO As Long, totalOccur As Long
        cntC = WorksheetFunction.CountIf(ws.Range("C4:C200"), Cell.Value)
        cntO = WorksheetFunction.CountIf(ws.Range("O4:O200"), Cell.Value)
        totalOccur = cntC + cntO
        
        ' 总出现次数大于1即为跨区域重复值，设置红色底色高亮
        If totalOccur > 1 Then
            Cell.Interior.Color = RGB(255, 0, 0)
        End If
    Next Cell
End Sub

运行注意事项 #

• 代码中引用的"Sheet1"必须替换为你实际存储C、O列业务数据的工作表名称，否则会弹出“找不到对象”的错误提示
• 运行前需将文档保存为启用宏的.xlsm格式，在Excel宏权限设置中允许运行当前文档内的代码
• 该代码的匹配规则为：只要同一个值在C4:C200、O4:O200两个目标区域内累计出现超过1次，对应位置的单元格就会被标红，完全适配两列值匹配的业务场景

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