Filtering on text colour

Feb 25th, 2002, 01:17 PM

Is it possible to filter a list to show only entries with a certain text colour? I am quite happy to put a formula in the next column to say "if Blue then 1, if red then 2" etc, and then filter on the number, but I don't know how to get the fontcolor.ndex = 3 stuff into the formula.

Mudface · Feb 25th, 2002, 01:34 PM

Try this (rather simplistic) approach. Open up the VB editor and Insert a standard module. Then copy and paste the following : -

Public Function ColourFilter(MyRange As Range)

ColourFilter = MyRange.Font.ColorIndex

End Function

In a cell to the right of your list, type

=ColourFilter(A2)

where A2 is the first entry in the list you want to filter. Then autofill down and filter by the numbers (eg 3 is Red, 32 is Blue, etc). Hope this is OK.

Feb 25th, 2002, 02:54 PM

Thanks for your reply - I've done as you suggest but am getting the #NAME? error back and Excel insists on changing my =ColourFilter(A2) to = colourfilter(A2). I guess it has to do with the Public Function piece.

Juan Pablo González · Feb 25th, 2002, 08:38 PM

Where did you insert the code ?. The CAPS shouldn't matter, Excel should recognize the formula if it was entered correctly.

Feb 26th, 2002, 03:16 AM

I inserted the code in a new Module in Personal.xls which is where I put all my code. When I type =ColourFilter(A2) in the spreadsheet I am now getting the "That name is not valid" warning.

Tim G

Mudface · Feb 26th, 2002, 03:29 AM

Is Personal.xls the spreadsheet where you have the data you want to be filtered? If it isn't you need to put the code into a new module in that spreadsheet, or at least have Personal.xls open at the same time. Also, review how to make an Add-In, so that all files on your machine can access the new function.

Feb 25th, 2002, 08:38 PM	#4
Juan Pablo González MrExcel MVP Join Date: Feb 2002 Location: Bogota, Colombia Posts: 11,927	Where did you insert the code ?. The CAPS shouldn't matter, Excel should recognize the formula if it was entered correctly. __________________ Regards, Juan Pablo González http://www.juanpg.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 25th, 2002, 01:17 PM	#1
Guest Posts: n/a	Is it possible to filter a list to show only entries with a certain text colour? I am quite happy to put a formula in the next column to say "if Blue then 1, if red then 2" etc, and then filter on the number, but I don't know how to get the fontcolor.ndex = 3 stuff into the formula.

Feb 25th, 2002, 01:34 PM	#2
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	Try this (rather simplistic) approach. Open up the VB editor and Insert a standard module. Then copy and paste the following : - Public Function ColourFilter(MyRange As Range) ColourFilter = MyRange.Font.ColorIndex End Function In a cell to the right of your list, type =ColourFilter(A2) where A2 is the first entry in the list you want to filter. Then autofill down and filter by the numbers (eg 3 is Red, 32 is Blue, etc). Hope this is OK.

Feb 25th, 2002, 02:54 PM	#3
Guest Posts: n/a	Thanks for your reply - I've done as you suggest but am getting the #NAME? error back and Excel insists on changing my =ColourFilter(A2) to = colourfilter(A2). I guess it has to do with the Public Function piece.

Feb 26th, 2002, 03:16 AM	#5
Guest Posts: n/a	I inserted the code in a new Module in Personal.xls which is where I put all my code. When I type =ColourFilter(A2) in the spreadsheet I am now getting the "That name is not valid" warning. Tim G

Feb 26th, 2002, 03:29 AM	#6
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	Is Personal.xls the spreadsheet where you have the data you want to be filtered? If it isn't you need to put the code into a new module in that spreadsheet, or at least have Personal.xls open at the same time. Also, review how to make an Add-In, so that all files on your machine can access the new function.