Help me sort my rows please!

September 11, 2007 2:26 PM

Is there a way to sort by "highlighted field" in Excel?

I have some rows I have highlighted in Excel. Some are highlighted green, some are highlighted yellow. I want to sort by highlight color, so all the greens are together, all the yellows are together, and all the non-highlighted rows are together.

Is this possible? If so, can you tell me how, because Excel Help was decidely unhelpful.

Thanks!

posted by BuddhaBelly to Computers & Internet (5 answers total)

I'm not aware of a direct way to do it. I would add a column that contains the color value of each cell's background and sort by that column. I am not aware of a built-in function to do this, so I'd create a user-defined function to get the background color of a cell.

Function GetColor(c1)
Application.Volatile
GetColor = c1.Interior.ColorIndex
End Function

(in VB editor, add a module to the VBAProject. Paste that function into it and save).

Then for example you can get the color value of cell A1 by putting =GetColor(A1) into another cell.

BTW- changing a cell's background color won't automatically update the value of =GetColor(A1), you have to push F9 after changing the background color.
posted by uberfunk at 2:41 PM on September 11, 2007

You're doing it wrong.

Adding a second column is the way to go, as uberfunk suggests.
posted by jeffamaphone at 4:35 PM on September 11, 2007

When I want to do something like this, I tend to add a column like the recommendations above.

For example, if I want to color rows that are over budget in red, I'll add a column where I can write "over budget" in that row. Then I create a conditional formatting rule that looks for the word "over budget" in my new column. It then turns the cell (or row) red based on the conditional formatting rule.

It sounds complicated, but it is really much more intimidating than it is difficult.

Once you get this nailed, you can then sort any which way you want, and the coloring happens automatically without a care on your behalf.

Good luck!
posted by scottso17 at 5:39 PM on September 11, 2007

With an additional column, you can also use filtering as well as sorting. That is why I hate formatting items as opposed to just putting the info in another column.
posted by slavlin at 6:30 PM on September 11, 2007

As others have said, this isn't something Excel can do easily and you can work around it.

But here is an addon for Excel that will let you sort based on cell colour if you really can't face working around it. Range > advanced sort is the tool you need.
posted by Ness at 6:52 AM on September 12, 2007