# EXCEL: Test if cell uses other cells in its formula

I was trying to conditionally format cells whose value didn't depend on anything else so I thought applying `ISFORMULA` to each cell would do the trick but then realized that if a cell has something like `=10^23` then it too, would be a formula even though its value is constant.

A cell with formula `=10+5` would have the function I'm looking for return FALSE and a cell with `=A5+3` would have the function return TRUE.

• It would be a hacky solution, but perhaps you could use the `FORMULATEXT` function to look for alphabetic characters. – davidmneedham Nov 6 '17 at 14:57
• I know you could do it with VBA.. Probably compare the cell's formula with the cell's text and show those that are equal to be true. Kind of depends what you are to do with the results though – Eric F Nov 6 '17 at 15:25
• No need for a hacky solution. Excel has a formula for this: =ISREF(value). It returns true if the formula contains a reference. But it sees =A1+A2 as normal, not reference. See also: techonthenet.com/excel/formulas/isref.php – LPChip Nov 6 '17 at 15:46
• It seems like the `Precedents.Count` property could be used, but I'm having trouble coming up with a full solution using this method. – davidmneedham Nov 6 '17 at 17:26

Here's a UDF that should work in most of the cases:

``````Function HAS_REF(r As Range) As Boolean
HAS_REF = (r.Formula <> r.FormulaR1C1)
End Function
``````

The `Formula` and `FormulaR1C1` properties of a range happen to be the same unless it contains a cell reference.

Exception: If the cell contains a reference to a named range, both will be equal and your cell won't get highlighted.

Update:

Here's a version that checks for range names as well:

``````Function HAS_REF(r As Range) As Boolean
Dim i As Long

HAS_REF = (r.Formula <> r.FormulaR1C1)

If HAS_REF Then Exit Function

For i = 1 To ThisWorkbook.Names.Count
If InStr(r.Formula, ThisWorkbook.Names(i).Name) Then
HAS_REF = True
Exit Function
End If
Next i
End Function
``````

Of course, if you're not using range names in your workbook, the function I posted earlier should suffice.

About `Precedents` attribute:

I tried using the `Precedents` attribute of the range to check for references. But it doesn't seem to work. I've tried the following:

• Directly using `r.Precedents` in the UDF to check if the range had any precedents. Assigning it to a range variable simply assigned the `r` (rather than `r.Precedents`).

• Using a helper sub routine to do the actual checking by calling the sub from function. That didn't work either. However, when you comment out the line in function that calls the sub procedure, the sub works and checks for precedents properly.

• Tried using global variables which are accessible by both the UDF and the helper sub routine. Doesn't make a difference.