# Highlight all cells that contain a formula instead of value or text

If you need to know which cells on the worksheet contain the formula, then you can use **Conditional Formatting**. In the picture below we have the following situation. In the range of 'A1:D10' cells there are values. Our goal is to **highlight the color of each cell in which the formula is located**. Those cells that contain only the value, number, or text should remain unmarked.

In the picture below, notice the actual situation when we include the representation of formula in the cells on the worksheet in Excel. If you want to **display formulas instead of values in cells**, then do the following: On the 'Formulas' tab in the 'Formula Auditing' command group, click '**Show Formula**'.

If you want the color to mark each cell that contains a formula you have two options.

- Create the named formula
- Create UDF Function (User Defined Function)

## 1. How to highlight the cell containing the formula using the named formula

The first example refers to the creation of a named formula, which you place in the Excel Name Manager.

These two formulas below return the same result:

On the **'Formulas**' tab, click '**Name Manager**' and create a new name item.

name: **IsFormula**

refers to: =ISFORMULA(INDIRECT("rc";FALSE))

or

name: **IsFormula**

refers to: =GET.CELL(48;INDIRECT("rc";FALSE)))

When you create a new item, it should look like the image below.

After creating and naming a new formula, your Name Manager looks like the picture below.

### 1a. Set color to highlight the cell background that contains the formula

The next step is to select the range of cells in which the data is located, on the worksheet. In this case, the range is 'A1:D10'. Start Conditional Formatting and set the color to **highlight each cell that you want to point out if the cell contains a formula instead of the values**. Select items to set the "**Use a formula to determine which cells to format**" rule and enter the formula =IsFormula(A1). Also, set the color format for a prominent cell containing the formula. It looks like the picture below.

After setting the format to highlight the background cells color of your '**Conditional Formatting Rules Manager** "looks like this in the picture below.

Finally, after finishing the rules and naming formula, note (the first image of this tutorial) that **Excel has highlighted all the cells that contain the formula**.

## 2. How to highlight a cell containing the formula using UDF function

Another way to highlight the background of cells containing formulas is to use a **VBA macro**, ie. **UDF function (User definition Function)**.

If you want to try one of the UDF listed below, copy it to your **VBE for Exce**l by inserting the **new Module** and pasting the source code. Select the entire cell range, run '**Conditional Formatting**' and set this expression =IsFormula(A1) into the rule field.

Thus the UDF prominent cells containing the formula are the following:

**UDF function 1.**

Function IsFormula(cell) As Boolean

'Excel 2013 and new

IsFormula = cell.HasFormula

End Function

**UDF function 2.**

Function IsFormula(cell) As Boolean

'Excel 2013 and new

If rr.HasFormula = True Then

MsgBox "Attention this cell contain formula!"

End If

End Function

**UDF function 3.**

Function IsFormula(ByVal Ref As Range) As Variant

If Ref.Cells.Count > 1 Then

IsFormula = CVErr(xlErrNA)

Else

IsFormula = Ref.HasFormula

End If

End Function

**NOTE!**

A workbook containing the formulas listed here or Excel UDF functions should be saved in the format: ***.xls or *.xslm or *.xlsb**

Finally, in the attachment of this tutorial, you have the Download file option on which I did this tutorial.

Pay attention, you may need as a delimiter of arguments formula use a comma (,) instead of a semicolon (;).

Copy formula to Notepad and with Find/Replace change semicolon to comma.