颜色代码使用交替颜色在excel的字段中重复输入 [英] Color code duplicate entries in a field of an excel using alternating colors

问题描述

我已经从MySQL数据库中提取了重复的列表到excel表。这个excel表明，我们根据Excel表单的一个字段，重复（〜1,900）和有时一式三份。

例如：

  10019 
 10019 
 10048 
 10048 
 10060 
 10060 
  

我如何才能对双重对进行着色，以便每对都可以轻松地将其视为一对。基本上，我想用交替的颜色填充每个重复的对，以便我可以很容易地看到这些对。

解决方案

你在说的是复制条带。一对

这个过程可以轻松地针对全行或行内数据块条带进行调整。

I have extracted a list of duplicates from a MySQL database to an excel sheet. This excel shows that we have duplicates (~1,900) and sometimes triplicate entries based on a single field of the excel sheet.

For example:

10019
10019
10048
10048
10060
10060

How can I go about coloring the duplicate pairs so that they can be easily visualized as a pair for every pair. Basically I would like to color fill each duplicate pair with an alternating color so that I could easily see the pairs.

解决方案

The process you are talking about is call 'duplicate banding'. A pair of Scripting.Dictionary objects should take care of this easily.

Sub colorDuplicateColor2()
    Dim d As Long, dODDs As Object, dEVNs As Object, vTMPs As Variant
    Dim bOE As Boolean

    Set dODDs = CreateObject("Scripting.Dictionary")
    Set dEVNs = CreateObject("Scripting.Dictionary")
    dODDs.CompareMode = vbTextCompare
    dEVNs.CompareMode = vbTextCompare

    With Worksheets("Sheet7")
        If .AutoFilterMode Then .AutoFilterMode = False

        With .Range(.Cells(1, "C"), .Cells(Rows.Count, "C").End(xlUp))

            With .Columns(1)
                .Cells.Interior.Pattern = xlNone
            End With

            With .Resize(.Rows.Count - 1, 1).Offset(1, 0)
                vTMPs = .Value2
            End With

            For d = LBound(vTMPs, 1) To UBound(vTMPs, 1)
                'the dictionary Items have to be strings to be used as filter criteria
                If Not (dODDs.exists(vTMPs(d, 1)) Or dEVNs.exists(vTMPs(d, 1))) Then
                    If bOE Then
                        dODDs.Item(vTMPs(d, 1)) = CStr(vTMPs(d, 1))
                    Else
                        dEVNs.Item(vTMPs(d, 1)) = CStr(vTMPs(d, 1))
                    End If
                    bOE = Not bOE
                End If
            Next d

            With .Columns(1)
                .AutoFilter Field:=1, Criteria1:=dODDs.Items, Operator:=xlFilterValues
                .SpecialCells(xlCellTypeVisible).Interior.Color = RGB(210, 210, 210)
                'use this to band the entire row
                '.SpecialCells(xlCellTypeVisible).EntireRow.Interior.Color = RGB(210, 210, 210)
                'use this to band the row within the UsedRange
                'Intersect(.Parent.UsedRange, .SpecialCells(xlCellTypeVisible).EntireRow).Interior.Color = RGB(210, 210, 210)
                .AutoFilter
                .AutoFilter Field:=1, Criteria1:=dEVNs.Items, Operator:=xlFilterValues
                .SpecialCells(xlCellTypeVisible).Interior.Color = RGB(255, 200, 200)
                .Cells(1).EntireRow.Interior.Pattern = xlNone
            End With

        End With

        If .AutoFilterMode Then .AutoFilterMode = False
    End With

    dODDs.RemoveAll: Set dODDs = Nothing
    dEVNs.RemoveAll: Set dEVNs = Nothing
    Erase vTMPs

End Sub

The data must be sorted on the duplicate criteria column of course.

      

This process could be easily adjusted for full row or row-within-data-block banding.

这篇关于颜色代码使用交替颜色在excel的字段中重复输入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！