MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Autofiltering based on Cell Formatting


Posted by Jeff Mischler on July 31, 2001 6:32 AM

We need to autofilter a spreadsheet based on cell formatting. Such as filtering on a "Bold" font. We found an article on this at the Mircosoft web-site, the example works, but we do not know how to minipulate the formula to work with our spreadsheet. Were using Excel 2000 and this is for inventory control, setting minimum values to trigger a Bold font and then filter the bold fonts to a smaller list. Thanks for your help!
Jeff Mischler


Posted by lenze on July 31, 2001 7:22 AM


I assume you are using conditional formatting to Bold your cells. If so, I don't believe you can use the Microsoft example because it only looks at the default or base format of the cells. You can still, however, filter the results based on value. See the help file for Advanced Filter and Criteria range examples. Use the Copy to new location option.

Posted by Mark W. on July 31, 2001 7:40 AM

Jeff, I believe that by following the instructions
provided my Microsoft you can achieve your
objective; however, if we are to be of much
help to you here you need to provide more details
about the organization of your worksheet? What's
your database range? What column contains the
bold values on which you want to filter?

Posted by Mark W. on July 31, 2001 8:03 AM

Good insight about conditional formatting! Now the
only question is does Jeff mean "cell formatting".

Posted by Jeff Mischler on July 31, 2001 8:07 AM

Our range is D7-343. The column is "D". Here is the formula of the example in the Microsoft article. =GET.CELL(20,OFFSET(INDIRECT("A2"),ROW()-2,0))
We have also used Conditional Formatting to format the font and color of the cells.
I have tried minipulating these values to work with our spreadsheet, any help would be greatly appreciated!

Posted by Mark W. on July 31, 2001 8:27 AM

> We have also used Conditional Formatting...

Conditional Formatting is quite different from
the "cell formatting" mentioned in your 1st posting.
Bottom-Line: It can't be done using Microsoft's approach.
See lenze's posting above

Of course, the fact that you've used conditional
formatting means that you could use the same
condition to create a new column of boolean
values, and then filter on it. : What's your database range? What column contains the