Excel VBA -颜色重复范围与不同的颜色

I have recurring tasks to do on big excel files at work and decided to use VBA for the first time to make my life easier. I found a code on internet, modified and adapted it to my own sheet and it works fine.

我在工作中经常重复使用大型excel文件，并决定第一次使用VBA让我的生活更轻松。我在网上找到了一个代码，把它修改并修改成我自己的表格，它运行得很好。

I have a column of IDs and I need to find the ranges of duplicates ID and group them. So far I did that manually and it took ages. This code effectively colors the duplicate ranges but it doesn't switch to a different color every time a new range appears.

我有一个ID列，我需要找到重复ID的范围并将它们分组。到目前为止，我是手工做的，而且花了很长时间。这段代码有效地为重复的范围着色，但不会在每次出现新的范围时切换到不同的颜色。

So let's say I have this : 001 001 001 002 002

假设有001 001 002

It will color the 001 in blue, and will color 002 in blue too. Not always but it happens and I don't want that.

它将使001用蓝色，将002也用蓝色。并非总是如此，但它确实发生了，我不想这样。

Here is the code :

这里是代码:

    Sub ColourDuplicates()
Dim Rng As Range
Dim Cel As Range
Dim Cel2 As Range
Dim Colour As Long


Set Rng = Worksheets("Sheet1").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
Rng.Interior.ColorIndex = xlNone
Colour = 6
For Each Cel In Rng


If WorksheetFunction.CountIf(Rng, Cel) > 1 And Cel.Interior.ColorIndex = xlNone Then
Set Cel2 = Rng.Find(Cel.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchDirection:=xlNext)
    If Not Cel2 Is Nothing Then
        Firstaddress = Cel2.Address
        Do
        Cel.Interior.Color = Colour
        Cel2.Interior.Color = Colour
            Set Cel2 = Rng.FindNext(Cel2)

        Loop While Firstaddress <> Cel2.Address
    End If


Colour = Colour + 1

End If
Next

End Sub

So, is it possible to make it change to a different color each time a new set of values appear ?

那么，是否有可能在每次出现一组新的值时将其改为不同的颜色呢?

Thank you very much !

非常感谢!

1 个解决方案

#1

Maybe you want to explore a "Worksheet_OnChange" ? If it is purely manual, it will do the job nicely

也许你想探索一个“Worksheet_OnChange”?如果它是纯手工的，它将很好地完成这项工作