COLOR SORTING via VBA CODE

Question

Any code for having all the colored cells in a column be sorted (grouped together) based upon their colors, no matter how many different colors there are existing in the same range, in any order? Because .SortOnValue.Colorrequires each and every different color to be separately pointed out in the order.

Answer 1

Faraz,

In the Excel file linked below, on my Dropbox public file, there is a bunch of code to count colors, including conditional formatting colors.  There is a function in there -

Public Function GetCellColor(theCells As Range, Optional theType As String = "Regular") As Long

This function returns the RGB (long integer) color value of the color in a cell, whether that color is a fill color or a conditional color.  You might be able to use it to get the color values of each of your cells, and then sort on those values.

HTH,

Eric

http://dl.dropbox.com/u/58499729/Count\_Sum\_by\_Color.xlsm

Answer 2

You'll need to test each cell to

   determine if it is conditionally formatted

   if so, which is active

   then figure out the color.

See http://www.xldynamic.com/source/xld.CFConditions.html#specific and also

http://www.cpearson.com/excel/cfcolors.htm

for some ideas on how to do this.

Answer 3

No, it won't. Conditional formatting in Excel 2007/2010 is so extensive that it'd be difficult to create a function that returns the currently applied conditional formatting color of a cell. The best I can think of is to create a formula that mimics the conditions for conditional formatting to return a value that uniquely identifies the condition that determines the color.

Answer 4

Thanx for the idea buddy! However, the same won't sort the conditionally formatted cells colored on conditions :(

Answer 5

Create the following function in a module in the Visual Basic Editor

Function ColorOf(rng As Range) As Long

    ColorOf = rng.Interior.Color

End Function

Let's say your data are in A2:A100.

If necessary, insert a blank column in column B.

Enter the following formula in B2:

=ColorOf(A2)

Fill down to B100.

Now sort the data on column B.