Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: Autofilter By Color

  1. #1
    Join Date
    21st January 2004
    Location
    Delhi
    Posts
    8

    Autofilter By Color

    Hi,
    I would like to get a code for autofiltering the data according to color. Is it possible?
    For eg. by the code i should be able to filter the values where it is red in color or yellow in color. I can findout the color index and give according to my need.

    Regards,
    Manoj

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: Autofilter Programming

    Hi Manoj,

    Try,

    VB:
    Sub FilterByColor() 
         'Thanks to jindon
        Dim r       As Range, txt As String 
        Dim fColor  As Integer 
         
        fColor = Range("B1").Font.ColorIndex 
        Columns(1).Rows.Hidden = False 
         
    Again: 
        For Each r In Range("a2", Range("a" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible) 
            If r.Font.ColorIndex <> fColor Then txt = txt & r.Address(0, 0) & "," 
            If Len(txt) > 245 Then 
                Range(Left(txt, Len(txt) - 1)).EntireRow.Hidden = True 
                txt = Empty: Goto Again 
            End If 
        Next 
        If Len(txt) Then Range(Left(txt, Len(txt) - 1)).EntireRow.Hidden = True 
    End Sub 
    
    
    If you want filter by cell color then

    change this

    VB:
    .Font.ColorIndex 
    
    
    with

    VB:
    .Interior.ColorIndex 
    
    
    change font color of B1 and run the code.

    HTH

  3. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,798

    Re: Autofilter Programming

    See Custom Functions and use Return Cell Color in another column and simply filter by it.

    POOR THREAD TITLE Please read THIS PAGE IN FULL
    Last edited by Dave Hawley; September 26th, 2007 at 19:45.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Change Color Of AutoFilter Drop Arrows
    By Obelix in forum Excel General
    Replies: 5
    Last Post: January 10th, 2008, 14:22
  2. Autofilter: Color Cell With Active Filter
    By mark smith in forum Excel General
    Replies: 3
    Last Post: September 27th, 2006, 17:09

Bookmarks

Posting Permissions

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