## Highlight numbers within specific ranges

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

The conditional formatting formula applied to cell range F3:F13 highlights all cells containing text. Here is how I did it: […]

Highlight overlapping date ranges using conditional formatting

The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]

Highlight records based on overlapping date ranges and a condition

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

Highlight dates in a date range

Question: How do I highlight dates that meet criteria using conditional formatting? Table of contents Highlight values in a column […]

This post decribes how to highlight records with closest value to a criterion. You can also choose to highlight the […]

This blog post shows you how to easily identify duplicate rows or records in a list. Conditional formatting formula: =COUNTIFS($B$3:$B$15, […]

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

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

Thanks Oscar, Huge help!