I have a large array of numbers and a row of numbers. I want each number in the array to become higlighted one color if it is within 4 of any number in the row and another if it is not. Is this possible? Something like Abs(anynumber in array-any number in row < 4, Highlight green, highlight red) I think I have to utilize index or match functions but im having difficulty. Thanks!

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

1. Select cell range (A4:D18)
2. Go to tab "
3. Click "Conditional formatting" button
4. Click "New Rule..."
5. Click "Use a formula to determine which cells to format"
6. Paste "green" formula in field
7. Click "Format" button
8. Go to tab "Fill"
9. Select a color
10. Click ok
11. click ok
Repeat step 4 to 11 with "red" formula.

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.