# Hightlight Cells With Formulas

Conditional Formatting can be used to identify cells that have formulas in them. This can be useful if you have a sheet with data entry cells mixed with computations by helping the user to see where they should be entering data.

I don’t know of any way to test a cell for a formula using built-in functions, so we’ll have to resort to a user-defined function (UDF).

Function CellHasFormula(rTarget As Range) As Boolean
If IsEmpty(rTarget) Then
CellHasFormula = True
Else
CellHasFormula = rTarget.HasFormula
End If
End Function

Why test for empty cells? I like to highlight cells where the user is supposed to enter data, not the other way around. For that reason, I generally couple the UDF with a NOT() function. I dislike negative fucntions, except NOT(), so I don’t write a CellDoesntHaveFormula() function. Just another peek into the depths of my insanity. In this example, I apply the conditional formatting to A2:E7.

## 12 thoughts on “Hightlight Cells With Formulas”

1. Jamie Collins says:

Dick,
I can see why you’ve done it but

If IsEmpty(rTarget) Then
CellHasFormula = True

Jamie.



2. David Wasserman says:

I am trying to use the CellHasFormula function in Conditional Formatting via VBA, but the procedure mysteriously quits after inserting the condition.

Strangely, if I do not include an equal sign before the condition formula, everything proceeds and then I have to manually go in and remove quote marks.

Here is the relevant codeblock:
With Cells(EndR, j)
.Select
FormatRange = jStr & StartR + 1 & “:” & jStr & EndR – 1
FormatFormula = “AND(NOT(CellHasFormula(” & Replace(.Address, “\$”, “”) & _
“)),COUNT(” & FormatRange & “)=0)”

Formula1:= FormatFormula

The variables used are:
j – column #
jstr – column in letters
StartR – Start of block above cell
EndR – End of block above cell

Notice that I do not currently have an equal sign (=) before the formula. If I put it in, the procedure abruptly quits.

I appreciate any assistance provided…

3. David Wasserman says:

I copied the old commented line…

4. Dick says:

I know just what you mean. Every time I uses I tell myself it’s time to think of a better way, but I never do.

5. Dick says:

David: I get the same result. I know that CF has problems with UDF’s in other workbooks, but I’ve never seen this before. If you replace the UDF with a built-in function, it works as expected. This is a bug, clearly. Here’s a cruddy workaround if you want. Put the UDF in an unused cell and refer to that in CF formula (I simplified some things for testing purposes).

Sub test()

Dim FormatRange As String
Dim FormatFormula As String

With Range(“A11?)
.Select

On Error Resume Next
.FormatConditions(1).Delete
On Error GoTo 0

Range(“f1?).Formula = “=CellHasFormula(” & .Cells(1, 1).Address & “)”

FormatRange = “A1:A10?
FormatFormula = “=AND(NOT(F1),COUNT(” & FormatRange & “)=0)”

.FormatConditions(1).Font.ColorIndex = 6
End With

End Sub

6. Juan Pablo says:

Well, this is weird… but found another workaround, tested in XP only though.

The trick is clearing the cell before adding the Conditional Format:

Sub Test2()
Dim FormatRange As String
Dim FormatFormula As String
Dim jStr As String, StartR As Long, EndR As Long, j As Long

Dim OldValue As Variant

jStr = “A”
StartR = 1
EndR = 11
j = 1

With Cells(EndR, j)

‘CF bug
OldValue = .Value
.ClearContents

.Select

On Error Resume Next
.FormatConditions(1).Delete
On Error GoTo 0

FormatRange = jStr & StartR + 1 & “:” & jStr & EndR – 1
FormatFormula = “=AND(NOT(CellHasFormula(” & .Address(0, 0) & “)),COUNT(” & FormatRange & “)=0)”

.FormatConditions(1).Font.ColorIndex = 6

‘Restore old value
.Value = OldValue
End With
End Sub

7. Dick says:

Good one, Juan. That’s way better than using a helper cell.

8. rzf says:

Why not avoid the UDF altogether and use David Hager’s version (via J-Walk’s page)

It is old xlm code, but I’m guessing it’ll be around forever. Maybe someone at the MVP summit would know otherwise??? It also does not count blanks as formulas as yours does, though I wonder if another GET.CELL function may reproduce that too.

9. David Wasserman says:

I tried rzf’s method and it worked perfectly!

Thanks to all for your assistance.

10. Note that David Hager’s version is dangerous when used with XL97 or any Mac version. Copying a cell formula that references an XL4M command and pasting into another sheet will cause XL to crash hard.

11. sjt says:

How do you conditional format range of cells such that if they have “True” you colour them green and “false” you colour them blue?

I am really new to VBA and would appreciate any help. Where do you insert this in the code?

Thanks

12. Dick says:

sjt: Go to Conditional Format from the Format menu. Make your first condition

Cell Value Is – equal to – TRUE

and set the format color to green. Make the second condition

Cell Value Is – equal to – FALSE