Sleeper: Sort by interior.colorindex

[austenr]

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]

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]

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]

I read that earlier but am unsure how to use the function. Perhaps someone can show me in an example.

[austenr]

here is a sample workbook

[austenr]

Can anyone explain the function in this procedure? Im baffled

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

[Zack Barresse]

There's no better instructions than on that page. Anyone else who tries to describe it probably would not do any better.

[austenr]

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]

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.