Is it possible to conditionally format a cell if the cell contains formula (to alert myself and other users when updating the cell)?
You can use conditional formatting to do this by using XLM and Range Names
- Define a Range Name IsFormula =GET.CELL(48,INDIRECT("rc",FALSE))
- Apply a conditional formatting cells testing for the formula, ie =IsFormula with a colour fill
Building on brettdj's answer, because I found the linked article quite difficult to follow:
- Create a new Conditional Formatting rule and select Use a formula to determine which cells to format
- Insert the following formula:
- If you want the rule to apply to the whole worksheet,
$1:$1048576as the range to apply to. Otherwise, you can enter any range.
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?".
You can try these:
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
To check if any cells in column A have any formulas:
- Highlight column A
- Go to Conditional Formatting > New Rule > Use a formula to determine which cells to format
- Use the ff. formula:
- Press F5 or Ctrl + G
- Click Special.
- Choose Formulas and click OK. This highlights all cells in the worksheet that contains formulas.
- Set up the format you'd like to use.
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.
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.
=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.
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:
- Select the range of cells you want to apply your conditional formatting to
- In Home -> Conditional formatting -> New rule: Use a formula to detect which cell to format
- In Format values when this formula is true put:
- Select the format you want
Tested using Excel 2010.