highlight cells with a value within 3 of other cells on the row

[cspgsl]

I am no Excel expert so I am looking to see if this is possible.

I have a table like you see here. On each row, I want to conditionally highlight any two cells in the Daily Yardage columns with the same colour if the values are within 3 of any other cell in any other Daily Yardage column within the same row.

In other words, I want to compare the values in G6, M6, S6, Y6 and AE6 and if any values are within 3 of another, change the colour on the two cells that are within 3.

I have manually done it here but the table is actually 31 rows (for a month) and I am trying different scenarios in each of the Tee and Hole columns.

Any help would be appreciated.
Thanks

 

[jasonb75]

When you apply the conditional formatting, the rule is applied relative to the activecell, in your screen capture above, I can't see that G6 is active, which potentially means that everything is out of alignment.

Try a different method.

Delete the rule, then create a new one applied only to G6 (single cell) using the correct formula from my earlier post.

Click the OK buttons until the editor closes, then, witihout moving away from G6, go in to edit the rule and then copy and paste the range to apply to from your post above.

Does that fix it?

 

[cspgsl]

Unfortunately the same results

 

[cspgsl]

I don't know if this is allowed, but I can send you the file if you wanted to try it.

 

[cspgsl]

I see it is catching some of the combinations but it is also highlighting cells that do not fall into the criteria

 

[jasonb75]

I don't know if this is allowed, but I can send you the file if you wanted to try it.

Normal practice is to post a link to your file on a site like dropbox after removing or fictionalising any confidential data.

 

[cspgsl]

Here is a link to the file in OneDrive. There is nothing confidential in the file

Yardages

 

[Fluff]

The problem is that you have a line break in the header column. Try
=COUNTIFS($G$4:$AE$4,"Daily"&CHAR(10)&"Yardage",$G6:$AE6,">="&(G6-3),$G6:$AE6,"<="&(G6+3))>1

 

[cspgsl]

Thanks for the reply Fluff.

I applied the change but the results only highlight the last column, AE, and then, it doesn't highlight the two cells that are within 3 of each other. You can see by the circles here. Also, row 15 shows AE 15 as highlighted where there are no qualifying cells.

 

[Fluff]

This is what I get

Yardages.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1		Par 3 Tee & Hole Placement Standards							Shaded area = < 3 yards difference when compared to other tees of the day
2
3			4						7						12						14						17
4			Tee	To Center	Hole	From Center	Daily Yardage		Tee	To Center	Hole	From Center	Daily Yardage		Tee	To Center	Hole	From Center	Daily Yardage		Tee	To Center	Hole	From Center	Daily Yardage		Tee	To Center	Hole	From Center	Daily Yardage
5	PIN
6	1	Monday	A	160	3	16	176		A	165	1	-16	149		A	178	1	-16	162		A	200	2	0	200		A	151	3	16	167
7	2	Tuesday	B	180	2	0	180		B	175	3	16	191		B	170	2	0	170		B	195	3	16	211		B	160	1	-16	144
8	3	Wednesday	C	153	1	-16	137		C	170	2	0	170		C	158	3	16	174		C	190	1	-16	174		C	142	2	0	142
9	1	Thursday	D	173	3	16	189		D	156	1	-16	140		D	185	1	-16	169		D	232	2	0	232		D	172	3	16	188
10	2	Friday	A	160	2	0	160		A	165	3	16	181		A	178	2	0	178		A	200	3	16	216		A	151	1	-16	135
11	3	Saturday	B	180	1	-16	164		B	175	2	0	175		B	170	3	16	186		B	195	1	-16	179		B	160	2	0	160
12	1	Sunday	C	153	3	16	169		C	170	1	-16	154		C	158	1	-16	142		C	190	2	0	190		C	142	3	16	158
13	2	Monday	D	173	2	0	173		D	156	3	16	172		D	185	2	0	185		D	232	3	16	248		D	172	1	-16	156
14	3	Tuesday	A	160	1	-16	144		A	165	2	0	165		A	178	3	16	194		A	200	1	-16	184		A	151	2	0	151
15	1	Wednesday	B	180	3	16	196		B	175	1	-16	159		B	170	1	-16	154		B	195	2	0	195		B	160	3	16	176
16	2	Thursday	C	153	2	0	153		C	170	3	16	186		C	158	2	0	158		C	190	3	16	206		C	142	1	-16	126
17	3	Friday	D	173	1	-16	157		D	156	2	0	156		D	185	3	16	201		D	232	1	-16	216		D	172	2	0	172
18	1	Saturday	A	160	3	16	176		A	165	1	-16	149		A	178	1	-16	162		A	200	2	0	200		A	151	3	16	167
19	2	Sunday	B	180	2	0	180		B	175	3	16	191		B	170	2	0	170		B	195	3	16	211		B	160	1	-16	144
20	3	Monday	C	153	1	-16	137		C	170	2	0	170		C	158	3	16	174		C	190	1	-16	174		C	142	2	0	142
21	1	Tuesday	D	173	3	16	189		D	156	1	-16	140		D	185	1	-16	169		D	232	2	0	232		D	172	3	16	188
22	2	Wednesday	A	160	2	0	160		A	165	3	16	181		A	178	2	0	178		A	200	3	16	216		A	151	1	-16	135
23	3	Thursday	B	180	1	-16	164		B	175	2	0	175		B	170	3	16	186		B	195	1	-16	179		B	160	2	0	160
24	1	Friday	C	153	3	16	169		C	170	1	-16	154		C	158	1	-16	142		C	190	2	0	190		C	142	3	16	158
25	2	Saturday	D	173	2	0	173		D	156	3	16	172		D	185	2	0	185		D	232	3	16	248		D	172	1	-16	156
26	3	Sunday	A	160	1	-16	144		A	165	2	0	165		A	178	3	16	194		A	200	1	-16	184		A	151	2	0	151
27	1	Monday	B	180	3	16	196		B	175	1	-16	159		B	170	1	-16	154		B	195	2	0	195		B	160	3	16	176
28	2	Tuesday	C	153	2	0	153		C	170	3	16	186		C	158	2	0	158		C	190	3	16	206		C	142	1	-16	126
29	3	Wednesday	D	173	1	-16	157		D	156	2	0	156		D	185	3	16	201		D	232	1	-16	216		D	172	2	0	172
30	1	Thursday	A	160	3	16	176		A	165	1	-16	149		A	178	1	-16	162		A	200	2	0	200		A	151	3	16	167
31	2	Friday	B	180	2	0	180		B	175	3	16	191		B	170	2	0	170		B	195	3	16	211		B	160	1	-16	144
32	3	Saturday	C	153	1	-16	137		C	170	2	0	170		C	158	3	16	174		C	190	1	-16	174		C	142	2	0	142
33	1	Sunday	D	173	3	16	189		D	156	1	-16	140		D	185	1	-16	169		D	232	2	0	232		D	172	3	16	188
34	2	Monday	A	160	2	0	160		A	165	3	16	181		A	178	2	0	178		A	200	3	16	216		A	151	1	-16	135
35	3	Tuesday	B	180	1	-16	164		B	175	2	0	175		B	170	3	16	186		B	195	1	-16	179		B	160	2	0	160
36	1	Wednesday	C	153	3	16	169		C	170	1	-16	154		C	158	1	-16	142		C	190	2	0	190		C	142	3	16	158
Yardages

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G6:G36,M6:M36,S6:S36,Y6:Y36,AE6:AE36	Expression	=COUNTIFS($G$4:$AE$4,"Daily"&CHAR(10)&"Yardage",$G6:$AE6,">="&(G6-3),$G6:$AE6,"<="&(G6+3))>1	text	NO

Check that you have applied it to the correct area.

 

[cspgsl]

hmmm... somehow, the following crept into the formula

=COUNTIFS($G$4:$AE$4,"Daily"&CHAR(10)&"Yardage",$G6:$AE6,">="&(XEM6-3),$G6:$AE6,"<="&(XEM6+3))>1