Excel

AutoFilter Column by Color

Ease of Use

Easy

Version tested with

2002 

Submitted by:

Zack Barresse

Description:

Filter an entire sheet based on the color of cells in a specific column. 

Discussion:

As often there is need for a feature that will let you use the AutoFilter feature with color rather than a comparative cell value. As there is currently no feature in Excel that will let you do that, this bridges the gap between the two and allows you to perform such an action. Note: This does not work with Conditional Formatting. 

Code:

instructions for use

			

Option Explicit Sub FilterByColor() Application.ScreenUpdating = False Dim cel As Range, rng As Range Set rng = Sheet1.Range("B2", Sheet1.Range("A65536").End(xlUp).Offset(, 1)) For Each cel In rng If cel.Offset(, -1).Interior.ColorIndex = 4 Then 'Green color, change if necessary '** Used to filter further along in the Sub Routine cel.Value = "Green" End If Next cel '** Start AutoFilter Process With Sheet1.Rows("1:65536") .AutoFilter '** Use temporary column 1 .AutoFilter Field:=2, Criteria1:="Green" End With Application.ScreenUpdating = True End Sub Sub UnFilterMe() Application.ScreenUpdating = False '** Check if the AutoFilter is on If Sheet1.AutoFilterMode Then '** If on, turn it off Sheet1.Cells.AutoFilter '** Also glear the "Green" cells Sheet1.Range("B2", Sheet1.Range("B65536").End(xlUp)).Clear '** If not, do nothing End If Application.ScreenUpdating = True End Sub Sub UnColorAll() '** Uncolor all cells, to reset With Sheet1.Range("2:65536") '** 0 is for no color .Interior.ColorIndex = 0 End With End Sub

How to use:

  1. From Excel, hit Alt + F11.
  2. From the VBE menu select Insert -> Module.
  3. Copy/Paste code into right pane.
  4. Go through commented code (green) and change where dictated appropriate; noted as such.
  5. Press Alt + Q
  6. Save file before running.
  7. Note: Default will dictate a blank column be directly to the right of the range in question, change as necessary.
 

Test the code:

  1. After installing code and making necessary changes, press Alt + F8.
  2. Choose FilterByColor.
  3. To Unfilter, press Alt + F8, run UnFilterMe.
  4. To uncolor all cells below Header row, press Alt + F8, run UnColorAll.
 

Sample File:

AutofilterByColorEx.zip 8.5KB 

Approved by mdmackillop


This entry has been viewed 271 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express