Color or Format a Formula Referenced Cells - Precedents

Got any Excel Questions? Free Excel Help

Color Formulae Reference Cells

Here is some VBA code that will color the precedent cells of any single cell formula.

Sub ColorCellReferences()

    Dim rFormulaCell As Range

     

    Set rFormulaCell = Sheet1.Range("A1")

    On Error GoTo PrecedentError

    With rFormulaCell

        .ShowPrecedents (False)

        .NavigateArrow True, 1, 1

        With Selection

            If .Address = rFormulaCell.Address _

            Then GoTo PrecedentError

            .Interior.ColorIndex = 6

        End With

        Application.Goto .Cells(1, 1)

        .ShowPrecedents (True)

    End With

    Exit Sub

PrecedentError:

     MsgBox "No  formula, or no formula with Precedents in cell"

     

End Sub

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft