## 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

Please try:

```
=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).

Answered By – pnuts

Answer Checked By – Marie Seifert (AngularFixing Admin)