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.

`FORMULATEXT`

function to look for alphabetic characters. – davidmneedham Nov 6 '17 at 14:57`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