# Conditional formatting if cells are within a range of each other

## Issue

Using Excel 2013. I have a long list of numbers in column A. I would like to conditionally highlight any cell that is in a range of +/- 5 with respect to the number above it OR the number below it.

For example, if I have 1, 1, 10, 20, 21, 22, 50 (in this order) the cells highlighted should be the ones containing only 1, 1, 20, 21, 22.

## Solution

``````=OR(ABS(A1-A2)<6,ABS(A2-A3)<6)
``````

The maximum difference allowed is +/-5 which is a little simpler to express in a formula as not as much as +/-6, hence `<6` in conjunction with `ABS` because of the `+/-`, ie the sign of the difference is not relevant. There are two possibilities (hence the `OR`), in the example A2 needs to be compared with both A1 and A3.

It is the nature of Conditional Formatting that it automatically steps through the relevant `Applies to` range, and applies the formula to each cell in turn. Just like the formula shown in a cell and copied down one row would automatically adjust to `=OR(ABS(A2-A3)<6,ABS(A3-A4)<6)`, so for CF.

If the formula in the cell were `=OR(ABS(\$A\$1-\$A\$2)<6,ABS(\$A\$2-\$A\$3)<6)` before copying the result in another cell would not differ (because the references have been fixed – with `\$`s). So with CF – with `\$` throughout then the entire `Applies to` range would be formatted according only to the values in A1:A3.

The same options for use of `\$` are available in CF as in a cell – ie making references absolute (\$) can be mixed with relative, both in different parts of a formula and within a single cell reference (ie Column and Row independently).