Conditional formatting based on hardcoded, formula and links to other worksheets

Question

Is someone able to write me a macro that does the following:

Conditionally formats cells as follows:

Hardcoded cells (including cells that are a formula but with two hard coded numbers e.g. =50/10) --> blue font

Cells that contain ANY link to another worksheet -->green font

Cells that use a formula function (e.g. SUM, IF, HLOOKUP etc) BUT do not have any links to other worksheets --> black font

Is this possible? Would appreciate any tips on how to do this quicker on a mammoth spreadsheet if not.

Thanks

Answer 1

Easy to rearrange the code to check for the link first:

Sub TestMacro2()

    Dim rngC As Range

    Dim rngA As Range

    Dim strA As String

    Set rngA = ActiveSheet.UsedRange

    rngA.SpecialCells(xlCellTypeConstants).Font.Color = vbBlue

    For Each rngC In rngA.SpecialCells(xlCellTypeFormulas)

        If InStr(1, rngC.Formula, "!") > 0 Then

            rngC.Font.Color = vbGreen

        Else

            On Error GoTo NextCheck

            strA = rngC.Precedents.Address

            rngC.Font.Color = vbBlack

            GoTo SheetRefsOnly

NextCheck:

            Resume CheckLinks

CheckLinks:

            rngC.Font.Color = vbBlue

        End If

SheetRefsOnly:

    Next rngC

End Sub

Answer 2

Give this a try:

Sub TestMacro()

    Dim rngC As Range

    Dim rngA As Range

    Dim strA As String

    Set rngA = ActiveSheet.UsedRange

    rngA.SpecialCells(xlCellTypeConstants).Font.Color = vbBlue

    For Each rngC In rngA.SpecialCells(xlCellTypeFormulas)

        On Error GoTo NextCheck

        strA = rngC.Precedents.Address

        rngC.Font.Color = vbBlack

        GoTo SheetRefsOnly

NextCheck:

        Resume CheckLinks

CheckLinks:

        If InStr(1, rngC.Formula, "!") > 0 Then

            rngC.Font.Color = vbGreen

        Else

            rngC.Font.Color = vbBlue

        End If

SheetRefsOnly:

    Next rngC

End Sub

Answer 3

Spot on!

Thank you so much!

Answer 4

Thanks Bernie you're a modern day hero.

This works perfectly except it is shading in black some HLOOKUP formulae that refer to a table array in a different worksheet which ideally I'd like to shaded green:

=HLOOKUP($B$2,'OTHERWORKSHEET'!$B$2:$G$419,ROW()-1,FALSE)

I'm being picky though you're a life saver!