excel 2010 vba亮点与不同颜色的单元格具有不同的重复值跨多个列 [英] excel 2010 vba highlight with different colors cells with different dupplicate 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 函数失败。将其替换为查找。以下子页面将在指定范围内找到任何重复项，并以不同的颜色突出显示。

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屋！