Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Filtering on text colour

  1. #1
    Guest

    Default

    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.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    Guest

    Default

    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.

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    Guest

    Default

    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

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •