Conditionally Format a Cell If It Contains Formula

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

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

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)