Option Explicit 
 
Sub FormatMixedConstants() 
     
    Dim RegEx As Object, RegCol As Object, RegMat As Object 
    Dim CountNumeric As Integer 
    Dim NumMatches As Long 
    Dim TestRange As Range 
    Dim Cel As Range 
     
    On Error Resume Next 
    Set TestRange = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas) 
    On Error GoTo 0 
     
     'If there are no formulas then exit sub
    If TestRange Is Nothing Then Exit Sub 
     
    Application.ScreenUpdating = False 
     
     ' The on error is a deliberate method to handle the non-existence of cell precedents
    On Error Resume Next 
     
    Set RegEx = CreateObject("vbscript.regexp") 
    With RegEx 
        .Global = True 
        .Pattern = "[^=+-/*^()]+" 
    End With 
     
    For Each Cel In TestRange 
        Set RegCol = RegEx.Execute(Cel.Formula) 
         ' Parse all formulas and look at text in between "+" .. "-" .. "*" .. "("   etc
        NumMatches = RegCol.Count 
        If NumMatches > 0 Then 
            For Each RegMat In RegCol 
                 ' If a discrete number is found then exit cell parsing
                If IsNumeric(RegMat) Then 
                    CountNumeric = 1 
                    Exit For 
                End If 
            Next RegMat 
            Set RegCol = Nothing 
             'if there are no cell precedents or no numeric cells then skip formatting
            If Not (CountNumeric > 0) Or Not (Cel.DirectPrecedents.Count > 0) Then 
                 'remove green formatting from forumla if cell has no constants
                If Cel.Interior.ColorIndex = 35 Then Cel.Interior.ColorIndex = xlNone 
            Else 
                 ' Format cells with numbers and cell references as green
                Cel.Interior.ColorIndex = 35 
            End If 
            CountNumeric = 0 
        End If 
    Next Cel 
    Application.ScreenUpdating = True 
    Set RegCol = Nothing 
    Set RegEx = Nothing 
    Set TestRange = Nothing 
End Sub