Macro for coloring cells based on contents

**serge_kashlik** · 12-31-2017, 10:17 AM

I have the following macro

Formula:

Sub ModelColoring()



Dim cell As Range, constantCell As Range, formulaCells As Range

Dim cellFormula As String



With Selection

    On Error Resume Next

        Set constantCell = .SpecialCells(xlCellTypeConstants, xlNumbers)

        Set formulaCells = .SpecialCells(xlCellTypeFormulas, 21)

    On Error GoTo 0

End With



    

If Not constantCell Is Nothing Then

    constantCell.Font.Color = vbBlue

End If



If Not formulaCells Is Nothing Then

    For Each cell In formulaCells

        cellFormula = cell.Formula

        

        If cellFormula Like "*.xls*]*!*" Then

            cell.Font.Color = RGB(0, 176, 80)

        ElseIf cellFormula Like "*!*" _

        And Not cellFormula Like "*~**" _

        And Not cellFormula Like "*+*" _

        And Not cellFormula Like "*-*" _

        And Not cellFormula Like "*/*" _

        And Not cellFormula Like "*^*" _

        And Not cellFormula Like "*%*" _

        And Not cellFormula Like "*>*" _

        And Not cellFormula Like "*<*" _

        And Not cellFormula Like "*=<*" _

        And Not cellFormula Like "*=>*" _

        And Not cellFormula Like "*<>*" _

        And Not cellFormula Like "*&*" Then

            cell.Font.Color = vbRed

        Else

        cell.Font.Color = vbBlack

        End If

    Next cell

End If

End Sub

I have problems with this line

Formula:

And Not cellFormula Like "*~**"

Basically I want all the cells with constants have one font color, those that perform a calculation to have another and those that derive their value from another sheet a third. However, the above mentioned line does not seem to recognize the multiplication symbol * for some reason and colors the cells that use values from other sheets and multiply them as if they are simply derived from another sheet (i.e. vbRed instead of black).

I am still quite new to VBA so if anyone could help I would appreciate it.
Thx in advance