Question:  I need to test a range of cells to see if one of the cells does not contain a formula. We have a shared spreadsheet and we need to know if someone has put in a "hard" value where a formula should be.

I'd prefer to have the background color of a cell changed to red if the cell contains a value where it should contain a formula.

Is this possible?

Answer:  Yes, this is possible. To test a range of cells for a missing formula, we will need to create a macro and something to trigger the macro to check for missing formulas.

Download Excel spreadsheet (as demonstrated below)

In the spreadsheet below, we've created a button that calls a macro to test a particular range for formulas. When it finds a cell that does not contain a formula, it changes the background color of that cell to red.

After we click on the button called "Test for formulas", the spreadsheet now looks like this:

As you can see, cell C6 contains a value so its background color changes to red. As well, the blank cell (C8) has its background color changed to red because a blank cell does not contain a formula.

The only cell whose background color does not change is cell C7. This cell does contain a formula.

Macro Code:

The macro code looks like:

Sub TestFormulas()

    Dim LResponse As Integer

    For Each cell In Range("C6:C8")
        If cell.HasFormula = False Then
            cell.Interior.Color = vbRed
        End If
    Next

End Sub