Conditional format if #value! in another cell?

Question

Operators enter a part number into a column in a table called SKU.

e.g, BORT14-200X50

The nominal dimensions are calculated from this using some simple MID functions. In this case, thickness is 0.200" and width is 50".

e..g. =MID([SKU],8,3)

Operators then enter measured dimensions. These are compared to the calculated nominal and are highlighted if they fall outside of specification (i.e. nominal ± specification).

One potential problem is if the operators enter the part number incorrectly. Simply adding an errant character results in a #VALUE! in the calculated fields and the the measured dimensions will not be conditionally formatted if they are out of spec.

So my first thought was to conditionally format the SKU field if the corresponding nominal fields have #VALUE! errors , but I couldn't wrap my puny little brain around just how to do that. I also tried to do it if the nominal value was not greater than zero, but that didn't seem to work.

My next idea was to do some data validation though it doesn't seem like there are any text options other than text length and this won't work because part numbers aren't always identical in that respect i.e. sometimes there are additional characters at the end.

TIA

Answer 1

Select the range where part numbers are entered. I'll assume in the following that this range starts in row 2.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format',

Let's say the calculated fields are in columns K and M.

Enter the formula =OR(ISERROR(K2), ISERROR(M2))

(The 2 here is the start row; if the data start in row 5, for example, change 2 to 5 in the formula)

Click Format...

Specify the desired formatting.

Click OK twice.

Answer 2

Hello HansV and all,

I am trying to use the same formula for conditional formatting pointed on another cell with output value displayed as : #VALUE!

I am trying to format the cell with this condition - as with no 'colour'.

Further, in  my case, I also have some other conditional formula checks on the same cell, but checks a different value and highlights the cell Red. Unfortunately the formula for the error is not working.

Any recommendation please?

Thank you!