Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question

4 Answers 4

up vote 2 down vote accepted

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
share|improve this answer
    
Sorry, I haven't tested your answer until now. This trick is simple and beautiful. Accepted! Thanks. (By the way, the link is broken.) –  wilson Oct 30 '13 at 7:14

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.
share|improve this answer
    
although i personally don't prefer to use VBA, the VBA solution is better in this case because the cell formatting can be changed dynamically when formula is entered into a cell (or vice versa). thanks. –  wilson Nov 1 '11 at 2:09

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?".

share|improve this answer

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.

share|improve this answer
    
Your answer is incomplete. I don't think you have tested. –  wilson Oct 30 '13 at 7:12
    
-1, this doesn't do anything. What is =HasNoFormula supposed to do? –  toryan Mar 27 at 23:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.