View Full Version : Solved: Need to sort Excel by Colored Row, and Cell values
frank_m
11-27-2010, 12:01 PM
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
11-27-2010, 12:02 PM
Ron de Bruin has a solution at http://www.rondebruin.nl/table.htm
frank_m
11-27-2010, 12:16 PM
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
11-27-2010, 12:27 PM
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.ColourCounter.html#sorting
frank_m
11-27-2010, 12:38 PM
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.
:bow: xldynamic.com certainly is an appropriate name
Bob Phillips
11-27-2010, 01:53 PM
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 :dunno
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.