PDA

View Full Version : Sleeper: Sort by interior.colorindex



austenr
09-07-2005, 08:47 AM
I have a column (F) which I need to sort. I want to group all of the cells that do not have a interior.colorindex together. In other words like all cells colored in blue. Any help would be appreciated.

gibbo1715
09-07-2005, 08:55 AM
Have a read of this, should answer your question http://www.cpearson.com/excel/SortByColor.htm

Also a great KB entry by XLD Here http://www.vbaexpress.com/kb/getarticle.php?kb_id=494

Killian
09-07-2005, 09:11 AM
hmmm... I was going to suggest using a temporary column to write the colorindex to and sort by that


Dim myRange As Range
Dim tempRange As Range
Dim c As Range
Set myRange = Range("A1:I16")
Set tempRange = Range("I1:I16")
For Each c In tempRange
c.Value = c.Offset(0, -3).Interior.ColorIndex
Next
myRange.Sort Key1:=Range("I1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
For Each c In tempRange
c.Value = ""
Next

but there's probably a clever way

austenr
09-07-2005, 09:12 AM
I read that earlier but am unsure how to use the function. Perhaps someone can show me in an example.

austenr
09-07-2005, 09:15 AM
here is a sample workbook

austenr
09-07-2005, 01:15 PM
Can anyone explain the function in this procedure? Im baffled :dunno

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

Zack Barresse
09-07-2005, 01:40 PM
There's no better instructions than on that page. Anyone else who tries to describe it probably would not do any better.

austenr
09-07-2005, 01:43 PM
I suppose that I did not express myself correctly. What does the function actually do and how do you get it to sort correctly? I cannot get it to sort at all.

Zack Barresse
09-07-2005, 02:12 PM
The functions should return the interger value of the color index (Excel assigns colors an index value), this is what is returned. If you sort on this column, you can do so Ascendingly or descendingly.

I also like xld's KB entry. Byundt also gave an entry to use for Conditional Formatting color index's, because those are not the same as standard formats.