Conditional Formatting Relative References Not Working

Copper Contributor

Hi - I am currently working on writing a conditional formatting rule that will highlight a cell if the value of a cell in column B (B1) is within 5% of another cell (A1) in column A.  I've been able to figure out this formula: =OR($A1*1.05<$B1, $A1*0.95>$B1)

 

The above conditioning formatting rule works perfectly when I compare A1 to B1 but when I try to copy the formula to subsequent cells to compare A2 to B2, A3 to B3, the formula does not work as it continues to reference the A1 cell to compare itself to cells in the B column.  Could anyone help me figure out what I am doing incorrectly to get this formatting rule to work? I do not want to have to recreate this formula for each cell in order to get the conditional formatting to work.

 

Thanks!

3 Replies

@Laura_B ,

 

Your formula shows if A is on more than 5% more or less than B, and you have correct highlighting for that. Perhaps you need another formula, like here

image.png

 

@stv1vy , if use relative reference when values in B will be compared with ones in C