This macro will highlight all of the cells in a worksheet which contain a formula. The first one listed will highlight all of the cells with formulas within the active worksheet. The second macro listed will highlight all of the cells which contain a formula which are also within a predefined range of cells; to change this range simply change the cell references in this line of code For Each Rng In Range("A1:B25").
These macros, when run, will remove any previous color from the worksheet. So, if you have a very colorful worksheet and want to keep it that way, do not use this macro.
This macro is best used for when you have a large number of formulas and you're not sure if you know where they all are or if you inherit or receive worksheets from other people and you want to quickly locate all formulas.
To change the color of the highlight for the first macro, change the number in this line of code If r.HasFormula Then r.Interior.ColorIndex = 6 'yellow.
To change the color of the highlight for the second macro, change the number in this line of code Rng.Interior.ColorIndex = 3 'red.
Where to install the macro: Module
Highlight Cells with Formulas - Works on entire active worksheet
Sub Highlight_Formulas() 'Highlights all cells with formulas on the active sheet 'Will remove color from cells without formulas Dim r As RangeWith ActiveSheet.UsedRange
.Interior.ColorIndex = xlNone
For Each r In .Cells
If r.HasFormula Then r.Interior.ColorIndex = 6 'yellow
Next
End With
End Sub
Highlight Cells with Formulas - Works on Predefined Range of Cells
Sub Highlight_ Formulas_Range() 'Highlights all cells with formulas within a selected range 'Will remove color from cells without formulas Dim Rng As RangeFor Each Rng In Range("A1:B25") 'Range to highlight cells
If Rng.HasFormula Then
Rng.Interior.ColorIndex = 3 'red
Else
Rng.Interior.ColorIndex = 0 'blank
End If
Next Rng
End Sub
Excel VBA Course - From Beginner to Expert
200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
View Course
Similar Content on TeachExcel
Highlight Cells with Text or Formulas (non-empty cells)
Macro: This macro will highlight all cells in
excel which are not empty. This means that if a cel...
Count the Number of Cells that Contain Specific Text in Excel
Tutorial: How to count the number of cells that contain specific text within a spreadsheet in Excel....
Reverse Cell Contents (Mirror)
Macro: This macro will completely reverse the contents of any cell. This means that if you have a...
Highlight Rows that Meet a Certain Condition in Excel
Tutorial: In this tutorial I am going to cover how to highlight rows that meet a certain condition. ...
Understanding Formulas and Functions in Excel
Tutorial: In this tutorial I will cover the basic concepts of Formulas and Functions in Excel. A for...
How to Install the Macro
- Select and copy the text from within the grey box above.
- Open the Microsoft Excel file in which you would like the Macro to function.
- Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions. Or For other ways to get there,
Click Here.
- On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.
- If the Macro goes in a Module,
Click Here, otherwise continue to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step
8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.
- Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.
- You are now ready to run the macro.
Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Access 2007 More...Less
Use the Go To command to quickly find and select all cells that contain specific types of data, such as formulas. Also, use Go To to find only the cells that meet specific criteria,—such as the last cell on the worksheet that contains data or formatting.
Follow these steps:
Begin by doing either of the following:
To search the entire worksheet for specific cells, click any cell.
To search for specific cells within a defined area, select the range, rows, or columns that you want. For more information, see Select cells, ranges, rows, or columns on a worksheet.
Tip: To cancel a selection of cells, click any cell on the worksheet.
On the Home tab, click Find & Select > Go To (in the Editing group).
Keyboard shortcut: Press CTRL+G.
Click Special.
In the Go To Special dialog box, click one of the following options.
Comments | Cells that contain comments. |
Constants | Cells that contain constants. |
Formulas | Cells that contain formulas. Note: The check boxes below Formulas define the type of formula. |
Blanks | Blank cells. |
Current region | The current region, such as an entire list. |
Current array | An entire array if the active cell is contained in an array. |
Objects | Graphical objects, including charts and buttons, on the worksheet and in text boxes. |
Row differences | All cells that differ from the active cell in a selected row. There is always one active cell in a selection—whether this is a range, row, or column. By pressing the Enter or Tab key, you can change the location of the active cell, which by default is the first cell in a row. If more than one row is selected, the comparison is done for each individual row of that selection, and the cell that is used in the comparison for each additional row is located in the same column as the active cell. |
Column differences | All cells that differ from the active cell in a selected column. There is always one active cell in a selection, whether this is a range, row, or column. By pressing the Enter or Tab key, you can change the location of the active cell—which by default is the first cell in a column. When selecting more than one column, the comparison is done for each individual column of that selection. The cell that is used in the comparison for each additional column is located in the same row as the active cell. |
Precedents | Cells that are referenced by the formula in the active cell. Under Dependents, do either of the following:
|
Dependents | Cells with formulas that refer to the active cell. Do either of the following:
|
Last cell | The last cell on the worksheet that contains data or formatting. |
Visible cells only | Only cells that are visible in a range that crosses hidden rows or columns. |
Conditional formats | Only cells that have conditional formats applied. Under Data validation, do either of the following:
|
Data validation | Only cells that have data validation rules applied. Do either of the following:
|
Need more help?
You can always ask an expert in the Excel Tech Community or get support in the Answers community.