Is This Possible? Sorting by color? 1

[TulsaJeff]

I just got asked the question...."Will excel sort by color?"

I have built some macros that will set a color to certain items but have no idea if excel can actually take the colors and break them down to RGB values or whatever and then sort them accordingly.

Can someone help me out with this? Ya' Gotta Love It!

 

[JVFriederick]

I have used background colors to determine various situations. You can use this to look at all the combinations:

For IntR = 1 To 56
ActiveSheet.Rows(IntR).Columns(1).Interior.ColorIndex = IntR
Next IntR

I assume the font color is colored, not the background. Sorry, but I don't know how to determine the font color value.

 

[JVFriederick]

Whoops. Found out a little more.

For IntR = 1 To 56
ActiveSheet.Rows(IntR).Columns(1).Interior.ColorIndex = IntR
ActiveSheet.Rows(IntR).Columns(2) = "Test"
ActiveSheet.Rows(IntR).Columns(2).Font.ColorIndex = IntR
Next IntR

You should be able to take it from here.

 

[TulsaJeff]

can you elaborate a little more...this basically fills a column with the word "test" and colors each a different color.

I am not a programmer at all. I can record a macro and then do a few manipulations to that but anything beyond that I am in deep trouble.

I basically have a report that someone has color coded by simply coloring the font. It is a very long report and rather than trying to spend days trying to figure out the logic behind the color coding method I would like to set the rgb value in a adjacent column and sort the file by the rgb values.

is this something that should only be attempted by a veteran programmer or is there a better way to handle this...?

I am open for any and all suggestions at the moment.

Ya' Gotta Love It!

 

[JVFriederick]

Assume column A (#1) is available to use for color codes, and that column B (#2) is where the text has been color coded. The following code will determine the code of the text color used for 1000 records:

Sub DetermineColorCodes()

ColorCode = 1 ' Column number to store codes
TextColumn = 2 ' Column number that has colored text

For IntR = 1 To 1000
ActiveSheet.Rows(IntR).Columns(ColorCode) = ActiveSheet.Rows(IntR).Columns(TextColumn).Font.ColorIndex
Next IntR

End Sub


Now sort by the new code created in column A, including all of your data. (Skip rows in column A that go past your data or simply delete them.) All similar colors should now be grouped together. If you wish the order to be different, use A column again to manually enter and copy a new value so that when sorted a second time, the list is now in the desired order.

I think this should help?