Hey, Scripting Guy! We have an Excel spreadsheet in which we use different colors as a way to track status information. That works out really good for us, except for one thing: sometimes we’d like to sort the spreadsheet by color. As near as I can tell, however, Excel doesn’t let you sort a worksheet by color. Could a script help me out here?

— GD

Hey, GD. Could a script help you out here? You bet it can. (Ah, if only Adam Sandler would ask that same question before he makes his next movie.) The fact of the matter is that scripts are always useful. Remember the story of Ernest Shackleton? Well, back in 1915, when his ship (the Endurance) became trapped in the ice, Shackleton and his men were marooned in the frozen wasteland of Antarctica. How did they manage to escape certain doom? That’s right: Shackleton wrote a script that notified rescuers of their location, and saved them all.

Of course, there are two things that separate Ernest Shackleton from the rest of us. For one, we rarely have to cross 800 miles of treacherous ocean in a lifeboat. (Although many, many people at Microsoft have suggested that the Scripting Guys give that a try.) For another, any time we get into a bind (yes, any time) we can solve all our problems (yes, all our problems) simply by writing a script:

Const xlAscending = 1
Const xlYes = 1

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")
Set objWorksheet = objWorkbook.Worksheets("Sheet1")

i = 2

Do Until objExcel.Cells(i, 1) = ""
    intColor = objExcel.Cells(i,2).Interior.ColorIndex 
    Select Case intColor
        Case 3 intSortOrder = 4
        Case 4 intSortOrder = 1
        Case 6 intSortOrder = 2
        Case 41 intSortOrder = 3
    End Select
    objExcel.Cells(i, 3) = intSortOrder
    i = i + 1
Loop

Set objRange = objWorksheet.UsedRange
Set objRange2 = objExcel.Range("C2")
objRange.Sort objRange2, xlAscending, , , , , , xlYes

Set objRange = objRange2.EntireColumn
objRange.Clear

Before we launch into the details of the script and how it works, let’s set the stage a little. We’re assuming that GD has a spreadsheet that looks something like this:

Well, yes, now that you mention it, that is kind of pretty, isn’t it? But that’s not really important. What is important is that GD wants to be able to sort the worksheet so it looks like this:

As he noted, however, Excel doesn’t have a built-in method for sorting a worksheet by cell color. That’s true when it comes to scripting as well: there’s no straightforward way to use a script to sort cells by background color, either. So what are we going to do about that? The same thing Ernest Shackleton did: we’re going to cheat.

As you can see, our spreadsheet contains a list of names in column A and a bunch of colors in column B. What we’re going to do is determine which color appears in column B, then write an integer value representing that color in column C. Once we’ve done that, we’ll sort the worksheet on those integer values, then delete the contents on column C. The net effect? All the like colors will be grouped together, as if we had sorted the worksheet by color. (Which we did, in a somewhat roundabout way.)

Don’t worry; this will make more sense once we walk through the code. We start out by defining a pair of constants, xlAscending (which we’ll use to sort the worksheet in ascending order) and xlYes (which we’ll use to tell the script that our worksheet has a header row). After we define the constants we create an instance of the Excel.Application object, then set the Visible property to True; that gives us a running instance of Excel that we can see on screen. As soon as that’s done we use the following two lines of code to open the spreadsheet C:\Scripts\Test.xls, and to bind to the first worksheet in that file:

Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")
Set objWorksheet = objWorkbook.Worksheets("Sheet1")

That brings us to this line of code:

i = 2

What we’re going to do next is examine the value of column A for each and every row in the spreadsheet (or, to be more precise, each and every row in the spreadsheet that actually has a value in column A). We’ll use the counter variable i to keep track of our current row in the worksheet. Because our data doesn’t start until row 2 (row 1 is a header row), we set the initial value of our counter variable to 2.

So how do we look at the value of column A for each row in the spreadsheet (or, to be more precise, each and every row in the spreadsheet that actually has a value in column A)? That’s easy; we simply set up a Do loop like this one:

Do Until objExcel.Cells(i, 1) = ""

As you can see, we’re going to start out by looking at the value of cell A2 (row 2, column 1). If cell A2 is blank we’ll exit the loop. If cell A2 isn’t blank then we’ll execute this block of code:

intColor = objExcel.Cells(i,2).Interior.ColorIndex 
Select Case intColor
    Case 3 intSortOrder = 4
    Case 4 intSortOrder = 1
    Case 6 intSortOrder = 2
    Case 41 intSortOrder = 3
End Select
objExcel.Cells(i, 3) = intSortOrder
i = i + 1

In the first line of this code block we’re simply assigning the value of cell B2 (row 2, column 2) to a variable named intColor; we do that by grabbing the value of the cell’s Interior.ColorIndex property. Background colors are stored as integer values; in this case, we have the following values:

Our next step is to set up a Select Case block to take action based on the value of intColor. What kind of action are we going to take? We’re going to assign a sort order to each row based on the background color of the cell in column B. We want to sort our colors in the following order:

What does that mean? Well, yellow is the second color in the sort order. Suppose we look at cell B2 and it turns out to be yellow. (Which it is.) Yellow is color No. 2 in the sort order, so we’re going to set the value of cell C2 (row 2, column 3) to 2:

objExcel.Cells(i, 3) = intSortOrder

But how did intSortOrder get set to 2? That’s what we did in our Select Case block:

Case 6 intSortOrder = 2

As we noted earlier, yellow has an integer value of 6. In the poreceding line of code, we’re checking to see if the background color of cell B2 is equal to 6. If it is, then we assign the value 2 (sort order No. 2) to the variable intSortOrder.

After incrementing the value of the counter variable i by 1, we go back to the top of the loop and repeat the process with the next row in the spreadsheet. When all is said and done, our worksheet should look like this:

Now it’s time to sort the worksheet by “color” (that is, by the value in column C). That’s what these three lines of code are for:

Set objRange = objWorksheet.UsedRange
Set objRange2 = objExcel.Range("C2")
objRange.Sort objRange2, xlAscending, , , , , , xlYes

In line 1 we create an instance of Excel’s Range object, an instance that encompasses the range of cells in the worksheet that contain data. (That’s what the UsedRange property is for.) In line 2, we create a second range object, this one consisting of the single cell C2.

Finally, in line 3 we sort the worksheet, using the object reference to column C (objRange2) as the sort column, and telling Excel that we: 1) want to sort in ascending order (xlAscending); and, 2) have a header row (xlYes).

All we have to do now is to delete the values we added to column C. So how do we do that? Well, the easiest way is to create a range object that encompasses all the cells in column C. Because we already have a range object that points to cell C2 we can use the EntireColumn property to grab the remaining cells in that column:

Set objRange = objRange2.EntireColumn

After that we simply call the Clear method and delete all the values in column C:

objRange.Clear

The net result? Well, give it a try and you’ll see the net result.

And there you have it, GD. Is that more impressive than crossing 800 miles of treacherous ocean in a lifeboat? Probably not. But it’s awfully close.

P.S. We should probably note that the Scripting Guys are actually somewhat in awe of what Ernest Shackleton accomplished; pretty darn impressive, to say the least. In addition, we also feel a sort of kinship with Shackleton, or at least with the men who joined him on the expedition. According to an apocryphal story, those men joined the crew after responding to the following ad:

Amazingly enough, that’s the exact same ad the Scripting Guys responded to when they were hired by Microsoft!