totn Excel

MS Excel 2003: Test range of cells for formulas (or missing formulas)

This Excel tutorial explains how to write a macro to test a range of cells for missing formulas in Excel 2003 and older versions (with screenshots and step-by-step instructions).

Question: In Microsoft Excel 2003/XP/2000/97, 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.

Microsoft Excel

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

Microsoft Excel

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
Share on: