Excel Formulas: How to Color Formula Cells in Excel Automatically

This updated guide examines Excel Formulas: How to Color Formula Cells in Excel Automatically and organizes the essential facts, background, and practical takeaways in clear American English.

1. How to color formula cells in Excel automatically

At the Excel table interface to find the formula cell, we click on the Developer tab and then select Visual Basic as shown below.

Display the VBA interface, click Insert and select Module to display the code input interface.

Next, you enter the code below to filter and search for cells that use formulas in the table. Notice at the line of code Rng. Interior. ColorIndex = 36 , the number 36 is the color code so we can change to another color code if we want to use another color. Click Run to run the code.

Sub SelectFormulaCells() 'Updateby20140827 Dim Rng As Range Dim WorkRng As Range On Error Resume Next xTitleId = 'KutoolsforExcel' Set WorkRng = Application. Selection Set WorkRng = Application. InputBox('Range', xTitleId, WorkRng. Address, Type:=8) Set WorkRng = WorkRng. SpecialCells(xlCellTypeFormulas, 23) Application. ScreenUpdating = False For Each Rng In WorkRng Rng. Interior. ColorIndex = 50 Next Application. ScreenUpdating = True End Sub

Next, you localize the data in the KutoolsforExcel dialog interface and click OK to color.

The results of the cells that use the formula are colored as shown below. The color depends on the color code the user entered in the code.

2. Localize formulas in Excel

If you do not want to color in a table, just quickly highlight the range of data using the formula, just use the Go To Special tool .

We highlight the whole table and press the F5 key to open the Go To dialog box, then click Special to expand the custom.

Next to display the table, tick Formulas and click OK to identify the formula.

Then the data area using the zoned formula is identified as below.

:

• 4 basic steps for alternating coloring of lines in Microsoft Excel
• 4 basic steps to color alternating columns in Microsoft Excel