Conditional Formatting for Errors

[rhmkrmi]

Hi there,

I have a formula in column B that returns #N/A for some rows and #VALUE! for some others.
How can I use conditional formatting to format the cells differently depending on the formula result being #N/A or #VALUE!

Thank you.

 

[Phuoc]

For #N/A => =ISNA(B2)

For #VALUE! => =ISERR(B2)

 

[maabadi]

Select Range That you want , Go to Conditional formatting, Manage Rules, New Rule, Use formula to determine ...
and write ( i suppose B1 is your first cell have formula0:

=ISERROR(B1)

and Select format that you want and apply.

 

[Saba Sabaratnam]

You may try the following method of formatting your cells depending on error type.

For value error, use the following formula. Please note relative reference.

For NA error, use the following condition.

There will be two conditional formattings for one a cell as shown below.

Then copy and paste this conditional formatting to other cells that you want to format.


The final result will look like the following sheet.

Kind regards

Saba

 

[rhmkrmi]

You may try the following method of formatting your cells depending on error type.

For value error, use the following formula. Please note relative reference.


View attachment 29083

For NA error, use the following condition.

View attachment 29084


There will be two conditional formattings for one a cell as shown below.

View attachment 29085

Then copy and paste this conditional formatting to other cells that you want to format.


The final result will look like the following sheet.

View attachment 29086

Kind regards

Saba

Thanks a lot.
Great solution.

Where do you get the codes?

Thanks again!