excel 2010 vba 高亮显示不同颜色的单元格，在多列中具有不同的重复值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15177752/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
excel 2010 vba highlight with different colors cells with different dupplicate values across several columns
提问by Cris Reis
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.
如何使用不同颜色突出显示 excel 2010 中跨多列的重复单元格。我找到了这段代码，但它适用于一列。
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
回答by Jon Crowell
You need to change the range to cover multiple columns, which will cause your Matchfunction to fail. Replace it with Find. The sub below will find any duplicates in the specified range and highlight them with a different color.
您需要更改范围以覆盖多个列，这将导致您的Match函数失败。将其替换为Find. 下面的 sub 将找到指定范围内的任何重复项，并用不同的颜色突出显示它们。
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:
根据下面的评论添加结果的屏幕截图，以帮助阐明其工作原理。每组重复项都以单独的颜色突出显示。不重复的值不着色：
