Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
filter by colored cell?
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! -- Tina |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
filter by colored cell?
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 -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Tina" wrote: 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! -- Tina |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
filter by colored cell?
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 InsertModule. 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 On Mon, 12 Feb 2007 11:49:00 -0800, Tina wrote: 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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
filter by colored cell?
Ron de Bruin has an EasyFilter add-in that you can download, and use to
filter by colour: http://www.rondebruin.nl/easyfilter.htm Tina wrote: 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! -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct and colored cell as a condition to be met | Excel Worksheet Functions | |||
Cell shading with colored patterns | Excel Discussion (Misc queries) | |||
Colored Tabs | Excel Discussion (Misc queries) | |||
SUMIF cell is colored | Excel Discussion (Misc queries) | |||
get a colored text in one cell to appear the same way in others | Excel Discussion (Misc queries) |