Conditional Formatting for Errors

rhmkrmi

Active Member
Joined
Aug 17, 2012
Messages
308
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.
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,645
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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:
Excel Formula:
=ISERROR(B1)
and Select format that you want and apply.
 

Saba Sabaratnam

Board Regular
Joined
May 26, 2018
Messages
196
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.


1609822932597.png


For NA error, use the following condition.

1609822984848.png



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

1609823032467.png


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


The final result will look like the following sheet.

1609823086586.png


Kind regards

Saba
 

rhmkrmi

Active Member
Joined
Aug 17, 2012
Messages
308
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,861
Messages
5,627,314
Members
416,239
Latest member
Counselor85027

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top