Home > Excel Tutorial > Excel Vba Random Colors

Excel Vba Random Colors

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

Download Example File

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: