Conditional Formating with tolerances

Occasional Visitor
Hello, I am trying to add conditional formatting to a worksheet using a column as a nominal tolerance and then having a formula to determine if the other cell is large or smaller the cell will turn red. For example Column A has a specification and Column B has the measured value. I would like to do conditional formatting for column B. For example Column B > (column A +0.5) and Column B < (column A -0.5) the cell should turn red. However, in the worksheet Column B and Column A are always changing. I can not get the formulas to work as the results in the rows for each column changes.
1 Reply

@Jesswood 

Let's say you want to format B2:B100.

Select this range. B2 should be the active cell in the selection.

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

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

Enter the formula

 

=OR(B2<A2-0.5,B2>A2+0.5)

 

or

 

=ABS(B2-A2)>0.5

 

Click Format...

Activate the Fill tsb.

Select red.

Click OK, then click OK again.