In Microsoft Office Excel 2007 there are new commands on the Cell menu that make it easy to filter a table based on the active cell's value, font color, or fill color. This column discusses how you can access these features with a macro.

The Cell menu (see Figure 1) is displayed when you right-click a cell.

Note: There are two ways that a cell's font or fill color can be set. One is by using the Fill and Font controls in the Font group on the Home tab. The other way is by using Conditional Formatting in the Styles group on the Home tab. The great thing about the new color filtering features is that it works with colors set either way.

When you select one of the filter options on the Cell menu, Excel will attempt to guess what your filter range is if you've selected only a single cell. If you have any empty rows in your table, Excel may not select the range you intend.

In the simple example that follows, I demonstrate how to use one of the built-in options in the Visual Basic for Applications (VBA) code using the command bar’s Execute method. You may ask why not use the range’s AutoFilter method instead? The problem with the AutoFilter method is that it requires you to specify the font or fill color of the active cell. That's easy to do when the colors are set by the Font group controls, but when the colors are set by using conditional formatting, it is impractical, if not nearly impossible, to do so.

The reason for this is because Excel does not give you a direct way to tell in code what font or fill colors a cell is displaying as a result of conditional formatting. Any code would need to work through the conditional formatting rules and determine the rule in effect, if any, and then determine the formatting applied, if any. It is much easy to use the Execute method and have Excel do all of the work.

The following code example creates a new worksheet or workbook with every record with the same fill interior color, or pattern or shading style of the active cell. You can change the number in this part of the macro if you want to filter on the font color or value.

' Call the built-in filter option to filter on ACell
Application.CommandBars("Cell").FindControl(ID:=12233, Recursive:=True).Execute

Table 1. Filter Options

Note that using the control ID allows the code to work regardless of the language version of Excel you use.

Note: You can use the command bar tools at the following location to find control IDs: http://www.erlandsendata.no/downloads/commandbartools.zip. When you add the add-in in Excel 2007, you will find the menu on the AddIns tab. Click EDC Tools, click Commandbars, and then click List all Commandbars controls. Next select Cells(36-153), and then click OK.

The following macro filters on the fill interior color, or the pattern or shading style. To run the macro, do the following:

1. Open the Visual Basic Editor by clicking the Visual Basic button on the Developer tab or use the shortcut Alt-F11. If you do not see the Developer tab, click the Office button, click Excel Options, click the Popular tab, and then select the Show Developer tab in the Ribbon option.
   
   
2. On the Insert menu, click Module.
   
   
3. In the Project pane, double-click the module you just added and in the code window, paste the code procedure that follows and then close the Visual Basic Editor.
   
   
4. Select a cell with a fill interior color, or pattern or shading style, and then run the macro by clicking Macros on the Developer tab or use the shortcut Alt F8, highlighting Filter_Example_Excel2007, and then clicking the Run button. The macro will create a new worksheet for you with the filter results. Every time you run the code, it will delete the worksheet first so you are sure that the worksheet has the latest filter results.
   
   

Sub Filter_Example_Excel2007()
    Dim ACell As Range
    Dim WSNew As Worksheet
    Dim Rng As Range
    Dim ActiveCellInTable As Boolean

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    ' Delete the sheet MyFilterResult if it exists.
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("MyFilterResult").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    ' Remember the activecell.
    Set ACell = ActiveCell

    ' Test if ACell is in a Table or in a Normal range.
    On Error Resume Next
    ActiveCellInTable = (ACell.ListObject.Name <> "")
    On Error GoTo 0

    ' Optionally set the Filter range.
    If ActiveCellInTable = False Then
        'Your data is in a Normal range.

        ' If there are empty rows or columns in your data range, you
        ' can make sure that Excel uses the correct data range here.
        ' If you do not use these three lines, Excel will guess what
        ' your range is. Here we assume that A1 is the top left cell
        ' of your filter range and the header of the first column, and
        ' that C is the last column in the filter range.

        '        Set Rng = Range("A1:C" & ActiveSheet.Rows.Count)
        '        Rng.Select
        '        ACell.Activate
    Else
        ' Your data is in a Table.

        ' No problem if there are empty rows or columns if your data
        'is in a table so there is no need to set a range because
        'it automatically uses the whole table.
    End If


    ' Call the built-in filter option to filter on ACell.
    Application.CommandBars("Cell").FindControl _
            (ID:=12233, Recursive:=True).Execute

    ' Control Id      Description
    ' 12232          Filter by Selected Cell's Value
    ' 12233          Filter by Selected Cell's Color
    ' 12234          Filter by Selected Cell's Font Color
    ' 12235          Filter by Selected Cell's Icon

    ACell.Select

    ' Copy the Visible data into a new worksheet.
    If ActiveCellInTable = False Then
        On Error Resume Next
        ACell.Parent.AutoFilter.Range.Copy
        If Err.Number > 0 Then
            MsgBox "Select a cell in your data range"
            With Application
                .ScreenUpdating = True
                .EnableEvents = True
            End With
            Exit Sub
        End If
    Else
        ACell.ListObject.Range.SpecialCells(xlCellTypeVisible).Copy
    End If

    ' Add a new worksheet to copy the filter results in.
    Set WSNew = Worksheets.Add
    WSNew.Name = "MyFilterResult"

    With WSNew.Range("A1")
        .PasteSpecial xlPasteColumnWidths
        .PasteSpecial xlPasteValues
        .PasteSpecial xlPasteFormats
        Application.CutCopyMode = False
        .Select
    End With

    ' Close AutoFilter.
    ACell.AutoFilter

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

End Sub

If you want to create a new workbook with the filter results instead of a worksheet then change the following sections in the example macros.

Delete this part:

' Delete the sheet MyFilterResult, if it exists.
On Error Resume Next
Application.DisplayAlerts = False
Sheets("MyFilterResult").Delete
Application.DisplayAlerts = True
On Error GoTo 0

Replace this statement:

Set WSNew = Worksheets.Add

With this statement:

Set WSNew = Workbooks.Add.Worksheets(1)

In this column you saw how easy it is to filter a table by cell content, font color, or fill color by using VBA code.