Thursday, October 27, 2016

Excel Vba Random Coloring The 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 .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).