Question: I'm currently using this code to autocolour font based on its origin:

```
Sub Auto_Colour_Numbers()
   Dim rng As Range, rErr As Range
   On Error Resume Next
   For Each rng In Intersect(ActiveSheet.UsedRange, Selection)
       If rng.HasFormula Then
           Set rErr = Range(Mid(rng.Formula, 2, Len(rng.Formula) - 1))
           If CBool(Err) Then
               rng.Font.ColorIndex = 1 'black
           Else
               rng.Font.Color = RGB(0, 176, 80) 'green
           End If
           Err = 0
       ElseIf CBool(Len(rng.Value)) Then
           rng.Font.ColorIndex = 5 'blue
       Else
           rng.Font.ColorIndex = xlAutomatic 'default
       End If
   Next rng
   Set rErr = Nothing
End Sub

```

Basically it changes the font to blue if it's just a hard-coded number, black if it's a formula and green if it's coming from another worksheet

It works fairly well but there are a couple of problems:

1) If, for example, I have a number in cell A1 and then put the formula "=A1" in cell B1, the macro will turn the font green even though it's not being pulled from a separate worksheet

2) If I have a formula e.g. "=5+5" and then add onto that a cell linked from another sheet so that it become e.g. "=5+5+Sheet2!E8" it will still turn it black when ideally I'd like it to be green. I was thinking an if loop that looks for exclamation points might work for this?

Any help would be much appreciated (please assume no competence or knowledge of VBA in you answers as I am very much new to this!)

Thanks,

Thomas

Answer: 
Here's a no-VBA solution using conditional formatting.

1. To format the cells with a formula that points to another worksheet, create a format condition with the rule `=IFERROR(FIND("!",FORMULATEXT(A1)),FALSE)` (substituting the start of your actual data range for A1).
2. To format the hardcoded values, create another condition with rule `=ISNA(FORMULATEXT(A1))`.

The result of this is

[![enter image description here](https://i.stack.imgur.com/Pl7El.png)](https://i.stack.imgur.com/Pl7El.png)

where A1 is hardcoded, A2 is `=A1` and A3 is `=Sheet2!A1`.
If you're using a version earlier than 2013 here's a solution that doesn't use `FormulaText`: 

```
Public Function RefDifSheet(Target As Range) As Boolean

    If Target.HasFormula Then
        RefDifSheet = InStr(Target.Formula, "!") <> 0
    Else
        RefDifSheet = False
    End If

End Function

Public Function IsFormula(Target As Range) As Boolean

    IsFormula = Target.HasFormula

End Function

```

Then add three conditional format rules to your cell: 

* First rule: `=RefDifSheet(A1)`
* Second rule: `=IsFormula(A1)`
* Third rule: `=ISNUMBER(A1)`

I'm thinking it's probably also possible using *Macro4* functions and named ranges - haven't looked into that yet. (GET.CELLS - [using-excel-4-macro-functions](https://exceloffthegrid.com/using-excel-4-macro-functions/))