# MS Excel: Test range of cells for formulas (or missing formulas) in Excel 2003/XP/2000/97

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.

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