filter by colored cell?

[Guest]

Is there a way to filter my spreadsheet based on the background color of a
cell/row?

I have a very large spreadsheet with several hundered rows and about 6
different colored backgrounds. Each colored row has its own meaning. Is
there a way to filter so I can view only the blue rows or only the yellow
rows?

Thanks!

 

[Guest]

There's no built in method of doing this for XL versions earlier than XL
2007. However, there are some macros that can be written:
http://www.cpearson.com/excel/SortByColor.htm

If you want to avoid macros you may find it useful to assign a numerical
code to each color, code the colored rows accordingly, and filter on that
numerical code.

Dave

 

[Gord Dibben]

Tina

You can use a function to number the rows and filter on those numbers.

This function from Chip Pearson will do the trick.

Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer
'
' This function returns the ColorIndex value of a the Interior
' (background) of a cell, or, if OfText is true, of the Font in the cell.
'
Application.Volatile True
If OfText = True Then
CellColorIndex = InRange.Font.ColorIndex
Else
CellColorIndex = InRange.Interior.ColorIndex
End If
End Function

Usage is..........=CellColorIndex(A1) entered in helper column.

Drag/copy that to bottom of data range.

First off.........save a backup of your workbook.

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there.

Save the workbook and hit ALT + Q to return to Excel window.

Enter the formula in a helper column as explained above.


Gord Dibben MS Excel MVP

 

[Debra Dalgleish]

Ron de Bruin has an EasyFilter add-in that you can download, and use to
filter by colour:

http://www.rondebruin.nl/easyfilter.htm