Filtering on text colour

G

Guest

Guest
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.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top