View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Per Jessen[_2_] Per Jessen[_2_] is offline
external usenet poster
 
Posts: 703
Default #N/A Conditional Formatting affects all cells?

Hi

Your problem is that you are using an absolute cell reference, change
the formula to =ISERROR(A1)

Notice the $ signs has been removed to indicate that it is a relative
reference.

Regards,
Per


On 7 Jan., 01:04, SMW wrote:
I'm applying the conditional format =ISERROR($A$1) to change the text of
cells displaying #N/A to white, as recommended by every conditional
formatting guide on the Internet. *However, when applied to a range of cells,
they all turn white!!

Cell A1 has a formula that provides a #N/A error, in the same way that the
desired cells produce an #N/A error:

The cells that need to be white are coded =VLOOKUP(B4,SHEET1,10) where B4 is
='SHEET2'!C4. C4 is blank, so B4 returns 0. *The result is #N/A. *(When C4 is
no longer blank, B4 displays data and needs to be visible.)

This is the only conditional format used in the spreadsheet. *I've tried to
find out if anyone else has this problem and had no luck. *