Excel VBA Random Coloring The Duplicate Values

Excel VBA 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 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 :

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))

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



No comments:

Post a Comment