比较两列并设置具有不同颜色的匹配单元格的格式 [英] Compare two Columns and format matching cells with different colors

问题描述

感谢您在以下方面的帮助:

I would appreciate your help with the following:

我想比较两列，比如说A列和B列，**寻找重复项**.

I would like to compare two columns, let us say Column A and Column B, **looking for duplicates**.

 

如果A列中的值与B列中的值匹配，我想用颜色来格式化相同重复值的单元格(颜色是随机的，并且每次匹配都不同).

If a value in Column A has a match value in Column B, I would like to format the cells of the same duplicate value with the color (the colors are random and different for each match).

 

这是如果"A12 = B30"，则颜色将为红色.如果"A20 = B1"，则颜色为绿色，依此类推.

This is if `A12 = B30`, the color will be red. And if `A20 = B1`, the color is green and so on.

 

如果没有匹配项，则将其保留.

If there is no match just leave it as it.

 

那只是红色和绿色的一个例子.假设您有两列(A和B).

That was only an example for red and green. let say you have two columns (A and B).

A1 = 1000

A1 = 1000

A2 = 2000

A2 = 2000

A3 = 3000

A3 = 3000

A4 = 4000

A4 = 4000

A5 = 5000

A5 = 5000

A6 = 6000

A6 = 6000

A7 = 7000

A7 = 7000

A8 = 8000

A8 = 8000

A9 = 9000

A9 = 9000

 

B1 = 1500

B1 = 1500

B2 = 9000

B2 = 9000

B3 = 5000

B3 = 5000

B4 = 3500

B4 = 3500

B5 = 7500

B5 = 7500

B6 = 1000

B6 = 1000

B7 = 4000

B7 = 4000

 

因此，您有多个匹配项，而我需要每个匹配项都使用随机不同的颜色.例如:

So you have several matches and I need each match to be in random different colors. For example:

A1 = B6–>它们将用绿色上色/突出显示

A1 = B6  –> They will be colored/highlighted in green

A4 = B7 –>它们将被上色/突出显示为红色

A4 = B7   –> They will be colored/highlighted in red

A5 = B3>它们将以黄色上色/突出显示

A5 = B3 –> They will be colored/highlighted in yellow

A9 = B2 –>它们将用粉红色上色/突出显示

A9 = B2   –> They will be colored/highlighted in pink

 

任何匹配项的颜色都会不同，不匹配项的颜色会变少或不变.

The colors will be different for any match and the non-match will be color less or no change.

 

我希望这能解释问题，并且必须使用excel.

I wish this will explain the question and this has to be using excel.

{

Sub UsingCollection()
Dim cUnique As Collection
Dim Rng As Range
Dim Cell As Range
Dim sh As Worksheet
Dim vNum As Variant for at
Dim LstRw As Long
Dim c As Range, clr As Long, x

Set sh = ActiveSheet
With sh

    LstRw = .Cells(.Rows.Count, "A").End(xlUp).Row
    Set Rng = .Range("A1:B" & LstRw)
    Set cUnique = New Collection
    Rng.Interior.ColorIndex = xlNone
    clr = 3

    On Error Resume Next
    For Each Cell In Rng.Cells
        cUnique.Add Cell.Value, CStr(Cell.Value)
    Next Cell
    On Error GoTo 0

    For Each vNum In cUnique

        For Each c In Rng.Cells
            If c = vNum Then
                x = Application.WorksheetFunction.CountIf(Rng, vNum)
                If x > 1 Then c.Interior.ColorIndex = clr "error here: the code runs fine for around 50 lines then it is stoppedand gives error and pointing to this line"
                  //Error shows in pop window: Run-time error 'g': Subscript out of range
            End If
        Next c
        clr = clr + 1
     Next vNum

   End With


End Sub

}

推荐答案

这是我在此处给出的答案中经过调整的代码.

https://stackoverflow.com/a/33798531/1392235

遍历单元格以查找唯一值，然后循环遍历唯一值以对重复项进行着色.

Loop through the cells to find the unique values, then loop through the unique values to color the duplicates.

Sub UsingCollection()
    Dim cUnique As Collection
    Dim Rng As Range
    Dim Cell As Range
    Dim sh As Worksheet
    Dim vNum As Variant
    Dim LstRw As Long
    Dim c As Range, clr As Long, x

    Set sh = ActiveSheet
    With sh

        LstRw = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set Rng = .Range("A1:B" & LstRw)
        Set cUnique = New Collection
        Rng.Interior.ColorIndex = xlNone
        clr = 3

        On Error Resume Next
        For Each Cell In Rng.Cells
            cUnique.Add Cell.Value, CStr(Cell.Value)
        Next Cell
        On Error GoTo 0

        For Each vNum In cUnique

            For Each c In Rng.Cells
                If c = vNum Then
                    x = Application.WorksheetFunction.CountIf(Rng, vNum)
                    If x > 1 Then c.Interior.ColorIndex = clr
                End If
            Next c
            clr = clr + 1
        Next vNum

    End With

End Sub

结果

使用colorindex将我们限制为56种颜色，如果我们使用RGB，我们可以增加它.编辑代码的这一部分，您将不得不使用值来获得所需的颜色变化.

Using colorindex limits us to 56 colors, if we use RGB we can increase that. Edit this part of the code, you will have to play with the values get the color variances you like.

       If x > 1 Then c.Interior.Color = 1000000 + clr * 100
            End If
        Next c
        clr = clr + 255

这篇关于比较两列并设置具有不同颜色的匹配单元格的格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！