Formula for Conditional Formatting to match nos. in range

[naira]

Hi!
My data is as given below:
Data in Row 1:
89 68 68 97 84 64 77 93 67 75 63 55 82 64

Data in Row 3:
87 62 63 69 72 90 51 70 78 91 71 63 88 52

Below formula is being used for conditional formatting of range A1:N1. The formula identifies and highlights a number in the range A1:N1 that is Exactly 5 more than any of the numbers in range A3:N3.

=COUNTIF($A$3:$N$3, (A1+$J$5))

However, I need to modify the formula so that I can highlight any number in range A1:N1 that is upto 5 more or upto 5 less of any other number in range A3:N3.
For example, in the above example, the numbers 68, 63 and 55 should also be highlighted since they fall within the range of nos. 62, 63, and 52 in row 3.

The range of the numbers to be checked is entered in Cell J5 in the above formula.

PS: I tried with ABS, and range functions but could not get them to work.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have the FILTER function, try this.

22 01 05.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	89	68	68	97	84	64	77	93	67	75	63	55	82	64
2
3	87	62	63	69	72	90	51	70	78	91	71	63	88	52
4
5										5
CF Range

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:N1	Expression	=COUNT(FILTER($A3:$N3,ABS($A3:$N3-A1)<=$J$5,""))	text	NO

 

[naira]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have the FILTER function, try this.

22 01 05.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	89	68	68	97	84	64	77	93	67	75	63	55	82	64
2
3	87	62	63	69	72	90	51	70	78	91	71	63	88	52
4
5										5
CF Range

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:N1	Expression	=COUNT(FILTER($A3:$N3,ABS($A3:$N3-A1)<=$J$5,""))	text	NO

Hi Peter,

Sorry, didn't know about that option. Have updated the data.

No, Filter function is not available to me. Could you please suggest something else.

 

[Peter_SSs]

Have updated the data.

Thanks for that.

Try this instead.

22 01 05.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	89	68	68	97	84	64	77	93	67	75	63	55	82	64
2
3	87	62	63	69	72	90	51	70	78	91	71	63	88	52
4
5										5
CF Range (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:N1	Expression	=SUMPRODUCT(--(ABS($A3:$N3-A1)<=$J$5))	text	NO

 

[naira]

Thanks for that.

Try this instead.

22 01 05.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	89	68	68	97	84	64	77	93	67	75	63	55	82	64
2
3	87	62	63	69	72	90	51	70	78	91	71	63	88	52
4
5										5
CF Range (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:N1	Expression	=SUMPRODUCT(--(ABS($A3:$N3-A1)<=$J$5))	text	NO

Works Magically.
Thanks for your help @Peter_SSs.

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.