Format error cells

Is there an easy way to highlight all the cells that have error messages

By Neale Blackwood

There are a couple of ways.

Select cell A1, press F5, click the Special button. Select the Formulas radio button and uncheck all the entries under it except Errors. Click OK. You now have all the error messages selected and you can use Format Cells to select a colour for them.

The second method highlights errors automatically. This method uses conditional formatting and assumes you have no other conditional formats set up in the sheet.

To check to see if there are conditional formats in sheet select A1, press F5, click Special, and select the Conditional formats radio button and click OK.

Automatically highlighting errors will overwrite all other conditional formats. To proceed press Ctrl+ A to select the whole sheet. Then click the format menu and select Conditional Formatting. If there are entries or formats already entered, it means there are conditional formats in the sheet and it would be wise to click Cancel. Use the drop-down box to select “Formula is”. In the box to the right of Formula is, enter the formula:

=ISERROR(A1)

Then click the Format button and click the Patterns tab and select a colour. Click OK and OK again. Now all errors will be formatted with the colour you selected as they occur. They will revert to normal formatting when the error message disappears.

Extras

To use the above formula in Excel 2007 click the Conditional formatting button, click New Rule, Click Use a formula to determine which cells to format. Enter the above formula in the box and click the Format button, click Patterns tab and select a colour click OK twice.