Filtering on text colour

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

 

[Mudface]

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.

 

[Guest]

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]

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

 

[Guest]

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]

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.