Conditionally format a cell if it contains formula

Is it possible to conditionally format a cell if the cell contains formula (to alert myself and other users when updating the cell)?

2

6 Answers

There is a very simple way to do this, tested in Excel 2016.

Highlight your range you wish this to apply to, let's say from A3:W20. Go into conditional formatting and select NEW RULE | USE A FORMULA TO DETERMINE WHICH CELLS TO FORMAT.

Put in =isformula(A3) and pick the format you want to apply.

A3 is obviously a reference to the first cell in your range but this formatting then applies to all. The result is that within your range, any cell that is a formula is conditionally formatted.

1

Building on brettdj's answer, because I found the linked article quite difficult to follow:

1. Create a new Conditional Formatting rule and select Use a formula to determine which cells to format
2. Insert the following formula: =ISFORMULA(INDIRECT("rc",FALSE))
3. If you want the rule to apply to the whole worksheet, $1:$1048576 as the range to apply to. Otherwise, you can enter any range.

The formula INDIRECT("rc",FALSE) returns the reference of the current cell. If I ever use this in a sheet, I create a Defined Name called something like ThisCell and use that in the formula, just in case I ever come back years later and think "what the hell is this for?".

2

You can use conditional formatting to do this by using XLM and Range Names

I have a longer article on Using XLM with Range Names and Conditional Formatting to automatically format spreadsheets according to cell content

1. Define a Range Name IsFormula =GET.CELL(48,INDIRECT("rc",FALSE))
2. Apply a conditional formatting cells testing for the formula, ie =IsFormula with a colour fill

4

You can try these:

VBA

Create a custom function with the following code:

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

Example:

To check if any cells in column A have any formulas:

1. Highlight column A
2. Go to Conditional Formatting > New Rule > Use a formula to determine which cells to format
3. Use the ff. formula: =IsFormula(A1)

Non-VBA

1. Press F5 or Ctrl + G
2. Click Special.
3. Choose Formulas and click OK. This highlights all cells in the worksheet that contains formulas.
4. Set up the format you'd like to use.
   Or
   Go to Cell Styles (under the Home tab) and pick a style that you want to associate with formula-containing cells. To change the look-and-feel, right-click on the style that you selected and click Modify. All cells given this style will automatically be updated.

1

The newer help sites recommend a User-Defined Function:

Function IsFormula(cell) as boolean IsFormula = cell.HasFormula
End Function

Then use that function as your condition

And in fact, Excel 2013 and later has IsFormula as a standard function.

You can also use conditional formatting:

1. Select the range of cells you want to apply your conditional formatting to
2. In Home -> Conditional formatting -> New rule: Use a formula to detect which cell to format
3. In Format values when this formula is true put: =HasNoFormula
4. Select the format you want

Tested using Excel 2010.

2

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy