Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Autofilter By Color

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    Re: Autofilter Programming

    Hi Manoj,

    Try,

    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

    .Font.ColorIndex


    with

    .Interior.ColorIndex


    change font color of B1 and run the code.

    HTH
    Kris

    ExcelFox

    Comment


    • #3
      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, 19:45.

      Comment

      Trending

      Collapse

      There are no results that meet this criteria.

      Working...
      X