|
|
|
|
|
|
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
cel.Value = "Green"
End If
Next cel
With Sheet1.Rows("1:65536")
.AutoFilter
.AutoFilter Field:=2, Criteria1:="Green"
End With
Application.ScreenUpdating = True
End Sub
Sub UnFilterMe()
Application.ScreenUpdating = False
If Sheet1.AutoFilterMode Then
Sheet1.Cells.AutoFilter
Sheet1.Range("B2", Sheet1.Range("B65536").End(xlUp)).Clear
End If
Application.ScreenUpdating = True
End Sub
Sub UnColorAll()
With Sheet1.Range("2:65536")
.Interior.ColorIndex = 0
End With
End Sub
|
How to use:
|
- From Excel, hit Alt + F11.
- From the VBE menu select Insert -> Module.
- Copy/Paste code into right pane.
- Go through commented code (green) and change where dictated appropriate; noted as such.
- Press Alt + Q
- Save file before running.
- Note: Default will dictate a blank column be directly to the right of the range in question, change as necessary.
|
Test the code:
|
- After installing code and making necessary changes, press Alt + F8.
- Choose FilterByColor.
- To Unfilter, press Alt + F8, run UnFilterMe.
- 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.
|
|