## Highlight numbers within specific ranges

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

Counting conditionally formatted cells (vba)

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]

Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]

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, […]

How to highlight MAX and MIN value based on month

Conditional formatting formula to highlight max value in every month: =B2=MAX(IF(MONTH(A2)=MONTH(Date_rng), Close, "")) Conditional formatting formula to highlight min value […]

Here is how to highlight every other row using conditional formatting. Conditional formatting formula: =ISEVEN(ROW())*OR($B3:$D3<>"") Alternative CF formula: =EVEN(ROW())=ROW() This […]

Question: I have a list that I keep adding rows to. How do i create a border that expands as […]

Highlight records – multiple criteria [OR logic]

This blog post shows you how to highlight rows with multiple criteria using OR logic. The criteria is found in […]

Highlight duplicates in a filtered Excel defined table

You can highlight duplicates in an excel defined table using conditional formatting. However, that won´t work if you only want […]

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