Editor’s Note: To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com’s Spreadsheet Tips Page.
Reader Nancy P. wins an autographed copy of Learn Excel From MrExcel from CFO and MrExcel.com for her question, “Is there a way to use conditional formatting to format cells that are input cells, as well as formula?”
It would require a little bit of Visual Basic in order to use conditional formatting to highlight formula cells. However, using the steps in this article, you can apply static formatting to identify the formula cells and the input cells.
Static Formatting the Formula Cells
Follow these steps to highlight the formula cells in a worksheet:
1. Click in the box above and to the left of cell A1 to select all cells in the worksheet (Figure 1, below).
2. Press Ctrl+G to display the Go To dialog.
3. In the lower left of the Go To dialog, click the Special button to display the Go To Special dialog.
4. As shown below in Figure 2, choose Formulas and click OK. Only the formula cells will be selected.
5. Either apply any color from the paint bucket icon or open the Excel 2007/2010 Cell Styles gallery and choose the Calculation cell (Figure 3, below). All of the formula cells will be highlighted.
Fig. 1
Fig. 2
Fig. 3
Static Formatting Input Cells
The steps for formatting input cells are similar to the above. In Step 4, you should choose Constants and then uncheck Text, Logicals, and Errors, leaving only Numbers checked, as shown in Figure 4.
Fig. 4
Using a VBA Function for Conditional Formatting
There is an old trick to use a custom VBA function to detect if a cell has a formula. You can then use the custom function in the conditional formatting dialog.
To add the custom VBA function, follow these steps:
1. Type Alt+F11 to start the VBA Editor
2. From the menu, select Insert, Module
3. Type the following three lines in the VBA editor:
Function FormulaCell(AnyCell As Range)
FormulaCell = AnyCell.HasFormula
End Function
The screen should look like Figure 5.
Fig 5.
Then type Alt+Q to close VBA and return to Excel.
To use the custom function in conditional formatting, follow these steps:
1. Select the cells that should contain the conditional format. Note which cell is the active cell in the Name box to the left of the formula bar. You will need this cell address in step 5.
2. Type Alt+O+D to open the conditional formatting dialog.
3. Choose New Rule.
4. Choose Use a Formula to Determine Which Cells to Format.
5. In the Format Value Where This Formula Is True dialog, type =FormulaCell(A1). Instead of A1, use the cell address of the active cell that you noted in Step 1.
6. Click the Format… button and choose a format.
7. Click OK to close the Format dialog box. The screen should look like Figure 6 (below).
8. Click OK to close the Conditional Formatting dialog.
Fig. 6
Now, cells that contain a formula will be formatted. Note that the tiny VBA function will be deleted if you save the workbook with an .XLSX extension. You should choose to save with .XLSM or .XLSB instead.
Bill Jelen is a CFO contributing editor and author of 32 books about Excel, including VBA & Macros for Microsoft Excel. You can win a copy of one of his books if your question is selected for a column. Post your question to the community content block on the right.