## Highlight numbers within specific ranges

*Article updated on December 21, 2017*

**Answer:**

**Conditional formatting formulas**

Green:=OR(ABS(A4-$A$2:$D$2)<$F$1)

Red:=AND(ABS(A4-$A$2:$D$2)>=$F$1)

**How to apply conditional formatting formulas**

- Select cell range (A4:D18)
- Go to tab "
- Click "Conditional formatting" button
- Click "New Rule..."
- Click "Use a formula to determine which cells to format"
- Paste "green" formula in field
- Click "Format" button
- Go to tab "Fill"
- Select a color
- Click ok
- click ok

**Explaining "green" formula in cell A4**

*Step 1 - Subtract array of values ($A$2:$D$2) from value in cell A4*

A4-$A$2:$D$2

becomes

2-{34, 57, 93, 75}

and returns

{-32, -55, -91, -73}

*Step 2 - Return the absolute value of a number*

ABS(A4-$A$2:$D$2)

becomes

ABS({-32, -55, -91, -73})

and returns

{32, 55, 91, 73}

*Step 3 - Check if values are less than value in cell $F$1*

ABS(A4-$A$2:$D$2)<$F$1

becomes

{32, 55, 91, 73}<4

and returns

{FALSE, FALSE, FALSE, FALSE}

*Step 4 - Return FALSE only if all values are FALSE*

OR(ABS(A4-$A$2:$D$2)<$F$1)

becomes

OR({FALSE, FALSE, FALSE, FALSE})

and returns FALSE. Cell A4 is not highlighted green.

**Download *.xlsx file**

Merit.xlsx

### One Response to “Highlight numbers within specific ranges”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Thanks Oscar, Huge help!