Tuesday, August 29, 2006

My first Excel/VBA Lesson - Sort by Cell Background Color

The other day I was asked by a colleague to teach her the basics of programming in Excel with VBA. Here's the first task I gave her: write a script that a user can call to sort any selected range of cells by the color of their backgrounds. The reason I selected this task is that it seems fairly often written on on the web, so she would be sure to find plenty of examples.

In the end, with some help from me, she produced a fairly tight little script, which I'll be sending it out to those of you who've subscribed to the Excel_Geek Insiders service.

The basics are 1) insert a temporary column next to the range to be sorted, 2) set the value of each cell in that new column equal to the ColorIndex of the cells in the range to be sorted, 3) select the new column and range together, 4) sort by the new column, 5) delete the temporary column, and 6) reselect the original range to be sorted to give users a seemless feel.

Interesting, straightforward approach, I think.

No comments: