Solved: Need to sort Excel by Colored Row, and Cell values

[frank_m]

I need a way to Sort (Col 1) Empty Cell *No Color* 1st, Then Empty Blue 2nd, Then Blue with Value = "S"
(and each sort group sorted by Job Number in Col 2)

I've attached a sample before and after workbook.

I pref this to work in both Excel 2003 and 2007, but I can live with a just a 2007 version if the 2003 is very complicated.

Thanks so much for your help

Edit: replaced attachment with Rev2 version because I had the row height code too small

[Bob Phillips]

Ron de Bruin has a solution at http://www.rondebruin.nl/table.htm

[frank_m]

Hi xld

I really need to try hard to avoid an add-in for deployment reasons. I know Excel 2007 has more sorting options than 2003 so I'm ok only using 2007. - I'll do some googling for sorting by colored rows and see if I can get something close to what I'm after coded, instead of asking you to do it all.

I'll post back later for some help or work around ideas when I get that far

Thanks again.

(I replaced the attachment in my first post because the first version had code that was setting the row height too small)

[Bob Phillips]

Excel 2007/2010 has the option to sort by cell colour, it is in the Values dropdown.

In Excel 2003, the technique is to create a helper column and get the cell colour via a UDF, and sort by the helper column. I describe it in http://www.xldynamic.com/source/xld....r.html#sorting

[frank_m]

Thanks xld

Your 2003 technique looks very promising. I'll try playing with that first as it will be great to have it work in both 2003 and 2007

Many compliments to you for how clearly you explained your technique at the link you gave. Things like that make us slow minded struggles live a lot longer and enable us to have more time for smiles.

xldynamic.com certainly is an appropriate name

[Bob Phillips]

xldynamic.com certainly is an appropriate name

LOL!. I am not the creative type, and thought long and hard to come up with an appropriate name, that was the best I could come up with

When I created my blog, I named it 'Excel Do, Dynamic Does'. I have no more idea than the next guy what it means, but it was the first thing that entered my head, so I went with it.