Highlight a cell based on adjacent value only if it is greater than plus or minus 30 difference

I have a row of 20 numbers and am trying to create a rule that would highlight any cell that pluses up by 30 or more; or decreases by 30 or more compared to the adjacent cells value.

Highlight based on adjacent cell valuein the photo, you see Row 1 contains a standard time stamp. Row 2, is where the actual value would be entered every hour. B1 has a value of 10, C2 has a value of 50, which is +/- 30 different than B1, therefore it would auto highlight red. D1 is not more than +/-30 compared to C2, so will not highlight. Lastly, F2 is more than +/-30 compared to E2, so it will auto highlight. hope this helps. thanks

1 Answer

Lets say row 1 (A1-I1) are the cells you want to format. Start at cell B1:

Home> Conditional Formatting> New Rule> Use a formula to determine which cells to format

Format values where this formula is true

=ABS(B$1-A$1)>=30

which means highlight if the absolute value (if its a negative number it becomes a positive number. eg. -30 becomes 30) is greater than or equal to 30

then set the format by clicking Format...

then change the Applies to so it goes all the way to your last column:

Here is what you should get: