Filtering on text colour
Upcoming Power Excel Seminars
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.

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
  •  

 

 
DMCA.com