求助：用VBA实现两列值匹配高亮，忽略超额重复项

需求说明 #

需要用VBA实现：检查某一列的值是否存在于另一列中，仅高亮两列中数量较少的匹配次数对应的单元格，超额的重复值不高亮。例如某值在C列出现3次、E列出现5次，只高亮C列的3个和E列的前3个匹配单元格，剩下2个E列的该值不高亮。

原代码问题 #

尝试了以下代码，但出现错误高亮，未达到预期效果：

Sub highlightMatchingValues()

'Declare variables
    Dim cellC As Range, cellE As Range

'Loop through each cell with a value in column C
       For Each cellC In Range("C:C").Cells
        If Not IsEmpty(cellC) And cellC.Interior.ColorIndex = xlNone Then 'ignore empty cells and cells that are already highlighted

'Loop through each cell with a value in column E
     For Each cellE In Range("E:E").Cells
            If Not IsEmpty(cellE) And cellE.Interior.ColorIndex = xlNone Then 'ignore empty cells and cells that are already highlighted
                 If cellC.value = cellE.value Then 'check for a match

'Highlight both cells green
    cellC.Interior.Color = vbGreen
    cellE.Interior.Color = vbGreen


               End If
             End If
         Next cellE
    End If
    Next cellC

End Sub

原代码的核心问题：遍历C列每个单元格时，会匹配所有未高亮的E列单元格，导致单个C列值会多次触发E列高亮，最终高亮次数远超实际应匹配的数量。

修正后的代码 #

以下代码通过统计每个值在两列中的出现次数，按最小次数精准高亮对应数量的单元格：

Sub HighlightMatchingValuesProperly()
    Dim ws As Worksheet
    Dim colC As Range, colE As Range
    Dim cell As Range
    Dim valueCountsC As Object, valueCountsE As Object
    Dim currentVal As Variant
    Dim countC As Integer, countE As Integer, highlightCount As Integer
    Dim cHighlighted As Integer, eHighlighted As Integer
    
    ' 指定目标工作表（可根据实际修改）
    Set ws = ActiveSheet
    ' 获取两列的有效数据范围（避免遍历整列浪费资源）
    Set colC = ws.Range("C1", ws.Cells(ws.Rows.Count, "C").End(xlUp))
    Set colE = ws.Range("E1", ws.Cells(ws.Rows.Count, "E").End(xlUp))
    
    ' 创建字典统计各值出现次数
    Set valueCountsC = CreateObject("Scripting.Dictionary")
    Set valueCountsE = CreateObject("Scripting.Dictionary")
    
    ' 统计C列各值出现次数
    For Each cell In colC
        currentVal = cell.Value
        If Not IsEmpty(currentVal) Then
            If valueCountsC.Exists(currentVal) Then
                valueCountsC(currentVal) = valueCountsC(currentVal) + 1
            Else
                valueCountsC(currentVal) = 1
            End If
        End If
    Next cell
    
    ' 统计E列各值出现次数
    For Each cell In colE
        currentVal = cell.Value
        If Not IsEmpty(currentVal) Then
            If valueCountsE.Exists(currentVal) Then
                valueCountsE(currentVal) = valueCountsE(currentVal) + 1
            Else
                valueCountsE(currentVal) = 1
            End If
        End If
    Next cell
    
    ' 清除原有高亮，确保每次运行结果准确
    colC.Interior.ColorIndex = xlNone
    colE.Interior.ColorIndex = xlNone
    
    ' 处理C列：按最小匹配次数高亮
    For Each cell In colC
        currentVal = cell.Value
        If Not IsEmpty(currentVal) And valueCountsE.Exists(currentVal) Then
            countC = valueCountsC(currentVal)
            countE = valueCountsE(currentVal)
            highlightCount = WorksheetFunction.Min(countC, countE)
            
            ' 统计该值已高亮的数量
            cHighlighted = Application.WorksheetFunction.CountIf(colC, currentVal) - Application.WorksheetFunction.CountIfs(colC, currentVal, colC.Interior.ColorIndex, xlNone)
            If cHighlighted < highlightCount Then
                cell.Interior.Color = vbGreen
            End If
        End If
    Next cell
    
    ' 处理E列：按最小匹配次数高亮
    For Each cell In colE
        currentVal = cell.Value
        If Not IsEmpty(currentVal) And valueCountsC.Exists(currentVal) Then
            countC = valueCountsC(currentVal)
            countE = valueCountsE(currentVal)
            highlightCount = WorksheetFunction.Min(countC, countE)
            
            ' 统计该值已高亮的数量
            eHighlighted = Application.WorksheetFunction.CountIf(colE, currentVal) - Application.WorksheetFunction.CountIfs(colE, currentVal, colE.Interior.ColorIndex, xlNone)
            If eHighlighted < highlightCount Then
                cell.Interior.Color = vbGreen
            End If
        End If
    Next cell
    
    ' 释放对象
    Set valueCountsC = Nothing
    Set valueCountsE = Nothing
    Set ws = Nothing
End Sub

代码说明 #

1. 统计次数：用字典记录两列每个值的出现次数，避免重复计算
2. 计算高亮上限：取每个值在两列中出现次数的最小值，作为该值的高亮总数
3. 精准高亮：遍历每列时，统计该值已高亮的数量，未达到上限则高亮当前单元格
4. 前置清除：先清除原有高亮，避免历史结果干扰新的高亮逻辑

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