Excel Sorting by Cell Fill Color

[dschomburg]

Is there a way to sort data in Excel 97 by the background color of a cell?

Any help would help a heap !

Dave

 

[JVFriederick]

Sure, using a little bit of VB code.



Sub DetermineColorCodes()

ColorBack = 1 ' Column number to store codes
DataColumn = 2 ' Column number that has colored background

For IntR = 1 To 1000
ActiveSheet.Rows(IntR).Columns(ColorBack) = ActiveSheet.Rows(IntR).Columns(DataColumn).ColorIndex
Next IntR

End Sub

Note :
ActiveSheet.Rows(IntR).Columns(ColorBack) = ActiveSheet.Rows(IntR).Columns(DataColumn).Font.ColorIndex
will determine the text color code.


Now sort by the new code created on 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 column A again to manually enter and change an entire group to a new value so that when sort a second time, the list is now in the desired order.


If you want help with the VB code, let me know.

 

[dschomburg]

Thanks for the tip. I tried to access it last Friday but the server was down. Will this "Group" all different backgrounds together ?

 

[wiginprov]

This one intrigues me, but I get a Runtime error 438 - Object does not support this property or method. What did I do wrong?

 

[dschomburg]

Me too.
I typed everything exactly as above. Any Ideas?

 

[JVFriederick]

Bad hair day.

This will be better. Just change the remark around to pick the type of color, background or font. After the code places a value in column 1, you will need to sort the data, and then the same colors will be grouped together.



Sub DetermineColorCodes()

ColorBack = 1 ' Column number to store codes
DataColumn = 2 ' Column number that has color background OR text

For IntR = 1 To 1000
' Determine Font Color
ActiveSheet.Rows(IntR).Columns(ColorBack) = _
ActiveSheet.Rows(IntR).Columns(DataColumn).Font.ColorIndex

' Determine Background color
'ActiveSheet.Rows(IntR).Columns(ColorBack) = _
ActiveSheet.Rows(IntR).Columns(DataColumn).Interior.ColorIndex

Next IntR

End Sub

 

[dschomburg]

Brilliant! Thanks OODLES !

 

[wiginprov]

Excellent. Wow. That's great.