Excel 2010 VBA高亮显示具有不同颜色的单元格，跨多个列具有不同的重复值 [英] Excel 2010 VBA highlight with different colors cells with different duplicate values across several columns

问题描述

如何在Excel 2010中跨多个列以不同的颜色突出显示重复的单元格?

How do I highlight with different colors duplicate cells in Excel 2010 across multiple columns?

我找到了这段代码，但是它只适用于一列.

I found this code but it works for one column.

    Sub Highlight_Duplicate_Entry()
        Dim cel As Variant
        Dim myrng As Range
        Dim clr As Long
    
        Set myrng = Range("A2:A" & Range("A65536").End(xlUp).Row)
        myrng.Interior.ColorIndex = xlNone
        clr = 3

        For Each cel In myrng
           If Application.WorksheetFunction.CountIf(myrng, cel) > 1 Then
              If WorksheetFunction.CountIf(Range("A2:A" & cel.Row), cel) = 1 Then
                 cel.Interior.ColorIndex = clr
                 clr = clr + 1
              Else
                 cel.Interior.ColorIndex = myrng.Cells(WorksheetFunction.Match(cel.Value, myrng, False), 1).Interior.ColorIndex
              End If
          End If
       Next
    End Sub

推荐答案

您需要更改范围以覆盖多个列，这将导致您的Match函数失败.将其替换为Find.下面的子目录将查找指定范围内的所有重复项，并用不同的颜色突出显示它们.

You need to change the range to cover multiple columns, which will cause your Match function to fail. Replace it with Find. The sub below will find any duplicates in the specified range and highlight them with a different color.

用以下代码替换您的代码:

Replace your code with the following:

Sub Highlight_Duplicate_Entry()
    Dim ws As Worksheet
    Dim cell As Range
    Dim myrng As Range
    Dim clr As Long
    Dim lastCell As Range

    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set myrng = ws.Range("A2:d" & Range("A" & ws.Rows.Count).End(xlUp).Row)
    With myrng
        Set lastCell = .Cells(.Cells.Count)
    End With
    myrng.Interior.ColorIndex = xlNone
    clr = 3

    For Each cell In myrng
        If Application.WorksheetFunction.CountIf(myrng, cell) > 1 Then
            ' addresses will match for first instance of value in range
            If myrng.Find(what:=cell, lookat:=xlWhole, MatchCase:=False, after:=lastCell).Address = cell.Address Then
                ' set the color for this value (will be used throughout the range)
                cell.Interior.ColorIndex = clr
                clr = clr + 1
            Else
                ' if not the first instance, set color to match the first instance
                cell.Interior.ColorIndex = myrng.Find(what:=cell, lookat:=xlWhole, MatchCase:=False, after:=lastCell).Interior.ColorIndex
            End If
        End If
    Next
End Sub

根据以下评论添加结果的屏幕截图，以帮助阐明其工作原理.每套重复项以单独的颜色突出显示.不是重复的值不会被着色:

Adding a screen shot of the result based on a comment below to help clarify how this works. Each set of duplicates is highlighted in a separate color. Values that aren't duplicates are not colored:

这篇关于Excel 2010 VBA高亮显示具有不同颜色的单元格，跨多个列具有不同的重复值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！