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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.