4

We will see "#N/A" in the cell when we use formula that return error . i want to make a conditional format which will detect this error so if the cell contains #N/A then it will change the cell color.

I tried to use 'Text is exactly' or 'equal to' and use the value #N/A. But it doesn't work.

Is there any other way i can match this error string (#N/A) ?

andio
  • 1,574
  • 9
  • 26
  • 45

2 Answers2

7

In conditional formatting you can use this custom formula

=isna($C1)

or

=iserror($C1)

This example should work for the range set to C1:C100.

Change range to suit and see if this helps?

JPV
  • 26,499
  • 4
  • 33
  • 48
3

What worked for me across multiple cells is this custom formula:
=isna(INDIRECT(ADDRESS(ROW(),COLUMN())))
This means "Is the current cell equal to N/A?"

You can also use:
=iserror(INDIRECT(ADDRESS(ROW(),COLUMN())))
This means "Is the current cell an error?"

Andrew
  • 2,046
  • 1
  • 24
  • 37
DoctorDothraki
  • 31
  • 1