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.
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