Automatically color cells in Excel that contain a formula

How can I get Excel to automatically color cells that contain a formula?

For example, if cell B4 contains =SUM(B1:B3), cell B7 contains =B4-B7 then I would like to be able to automatically color them differently so Excel looks like this:

If I change cell B6 to a formula, then I expect it to automatically change color too.

3 Answers

It turns out you can use ISFORMULA with conditional formatting to do this.

From this site:

To apply conditional formatting that will highlight the cells with formulas:

• Select cells A2:C4, with cell A2 as the active cell.
• On the Excel Ribbon's Home tab, click the Condtional Formatting command
• Click New Rule
• Click Use a formula to determine which cells to format
• Enter and ISFORMULA formula, refering to the active cell -- A2: =ISFORMULA(A2)
• Click the Format button, and select a fill color for the cells with formulas -- gray in this example.
• Click OK, twice, to close the windows.

Unfortunately ISFORMULA only works in Excel 2016 and above.

However, inspired by the other answers, I realised you could create some VBA to emulate that function for people on earlier versions of Excel.

To do this, you need to open up the VBA editor (Alt+F11), create a new module (menu option "Insert", then "Module") and put this code into that module:

Public Function IsFormula(ref As Range) IsFormula = ref.HasFormula
End Function

Save it and now the conditional formatting will work.

There are two parts to this.

1. You already have a sheet with formula.
2. Going forward anything you type in as a formula in the same sheet.

I suggest a VBA solution as follows.

Press ALT + F11 to access the VBA editor. Insert a Module from the Insert Menu. Go to its Code Window and paste the following code into it.

Sub ColorFormula()
Dim inrange As Variant
Dim incell As Range
On Error Resume Next
Set inrange = Application.InputBox(Prompt:="Please Select a Range", Type:=8)
If inrange.Rows.Count = 0 Then MsgBox ("No Range Selected!") End
End If
For Each incell In inrange If incell.HasFormula = True Then incell.Font.Color = -4165632 End If
Next
End Sub

Now from the Left Pane, click ThisWorkbook and select WorkBook SheetChange Event in the code window. A Placeholder for subroutine with End Sub shall be available for you to inert your code into it.

Paste the following code into it

If Target.HasFormula = True Then Target.Font.Color = -4165632
End If

In this example, Blue color is selected, you can change it to any other available color.

Exit the VBA Editor. Now every time you change a cell in any of the sheets of that workbook, the SheetChange event will fire and if it's formula, it will change to Blue font.

Press ALT + F8 and run the ColorForlmula Macro and specify the cell range. The code will run thru each cell in the range and if already existing formula is found, it shall change the font to Blue.

This small event macro:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count > 1 Then Exit Sub If Not Target.HasFormula Then Exit Sub Cells.SpecialCells(-4123).Font.ColorIndex = 5
End Sub

will automatically color the font of a cell blue when a formula is entered.
(It will also color all other formula cells blue at the same time)

Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

and

(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

Macros must be enabled for this to work!