VBA – Highlight cells with formulas

Posted on November 8, 2014 by

In the current article, I will show how to highlight only these cells, which contain formulas in the current Excel sheet. This can be useful, for a better orientation in the spreadsheet.

With the property:
.SpecialCells(xlCellTypeFormulas)
we quickly grasp, whether a cell contains formula or not. Thus, we only need a loop, for each cell in the active workbook and coloring the cell in case it contains a formula. In our case we use “10”, which is green.

Here comes the code:

Sub <span class="hiddenSpellError">FindFormulas</span>()

    Dim <span class="hiddenSpellError">ws</span> As Worksheet
    Dim cell As Range

    Set <span class="hiddenSpellError">ws</span> = <span class="hiddenSpellError">ActiveSheet</span>
    For Each cell In ws.Cells.SpecialCells(<span class="hiddenSpellError">xlCellTypeFormulas</span>)
        cell.Interior.ColorIndex = 10
    Next cell

End Sub

Sub FindFormulas()

Dim ws As Worksheet

Dim cell As Range

Set ws = ActiveSheet

For Each cell In ws.Cells.SpecialCells(xlCellTypeFormulas)

cell.Interior.ColorIndex = 10

Next cell

End Sub

🙂