Excel VBA Random Coloring The Duplicate Values

Excel VBA Highlight Duplicate Values

       
excel highlight duplicate values

          Sample workbook contains two sheet and different two example macro.Dictionary Collection Object was used in each two macro - Set Evn = CreateObject("Scripting.Dictionary") -

➥ In first example(Example1 Sheet) ago,the used range columns are sorted ascending according to cell A2 .The used codes :
ActiveSheet.Cells(2, Cells(Rows.Count, lst_column).End(xlUp).Row).Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom 

Later duplicate values background are filled by same color (according to the desired colors) . Color index number can be selected between 1 to 56. This numbers are assigned to array :
Set Evn = CreateObject("Scripting.Dictionary")
For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
        If Not Evn.exists(Cells(i, 1).Value) Then
Colors = Array(2, 4, 6, 7, 8, 12, 15, 16, 17, 19, 20, 22,24, 27, 28, 33, 34, 35, 36, 37, 38, 39, 42, 43, 44, 45, 46, 48)
Clr = Colors(Int((UBound(Colors) - LBound(Colors) + 1) * Rnd))
 Range(Cells(i, 1), Cells(i, lst_column)).Interior.ColorIndex = Clr
            Evn.Add Cells(i, 1).Value, 1
                Else
            Range(Cells(i, 1), Cells(i, lst_column)).Interior.ColorIndex = Clr
        End If
Next i

➥ In second example(Example2 Sheet) , only duplicate values' background in Column A are filled by same color . Unique value's background color doesn't change (white color).

excel highlight duplicate values with random colors


No comments:

Post a Comment