Conditional format to cells containing a #DIV/0! error

  • Thread starter Thread starter PiPPo
  • Start date Start date
P

PiPPo

I would like to apply a conditional format to an entire spreadsheet.
In particular, if a cell returns and error such as #DIV/0! I would the
selected font color to be white.

What do I have to enter in the conditional formatting menu? If I set
the value of the cell equal to #DIV/0!, the format does not work.

A solution that I found could be to use the GoTo functions to select
the cells with errors and then manually apply the color, but since I am
creating a template at a later point the empty cells that return the
divided by zero error may contain a number. Basically I would like the
cells to automatically update.

Can anyone please help?

Thanks a lot,

PiPPo
 
One way,

click in the select all button above row 1 header and to the left of column
A header to select the whole sheet or type i:65536 in the name box (above
column A), do format>conditional formatting, select formula is and type

=ERROR.TYPE(A1)=2

click the format button and select white fonts and click OK twice


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com
 
You could uses a formula in the Format|conditional formatting dialog:

Formula is:
=ISERROR(A1)

(if A1 is the cell)
 
Select the cells to format.

Then Format>CF>Formula is: =ISERROR(activecell)

Make sure Excel does not place $ signs around the cellref.

Pick the white font and OK your way out.


Gord Dibben MS Excel MVP
 
Hi there,

why don't you use :
=IF(IsError(Function),"",Function)
if the calculation (function or division, etc) gives an error message (e.g.
#DIV/0!) then results "blank", if can be done, then does the function.

Any good?
Best regards,
ANdras
(Hungary)
 
Of course that will hide all errors but it might be what the OP wanted
albeit not asked for? <bg>


Peo
 
Back
Top