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

 

[cspgsl]

Well, that returned some results but, not what I intended as it shows differences that don't relate to the +/- 3

I really appreciate your help, thank you

 

[jasonb75]

Possibly the wrong cell was active when you changed in, resulting in the relative references being off.

Select G6, then go to Conditional formatting > Manage rules.
Change the dropdown from 'Current Selection' to 'This Worksheet' then re-enter the formula.

 

[cspgsl]

That changed some of the references but, it is essentially the same. It highlights cells within the same row that is greater than 3

 

[jasonb75]

I'm not seeing anything else that could cause the errors, I'll have another look later when I get home.

If possible, could you post an up to date set of screen captures like the ones in post 5 please.

 

[cspgsl]

Here is the latest, but also had a thought overnight, which I have posted below this screenshot.

What if the range was the single row vs. all of the rows?

 

[jasonb75]

Not sure what you mean, the way that the fomula is written, it will only look at the same row.

Could you post captures of the conditional format window as well please (showing the formula and the range applied to as before).

 

[cspgsl]

 

[cspgsl]

I was grasping at ideas. maybe applying it to one set of cells in a row would provide the result.

 

[jasonb75]

Looking at the screen capture with the formula, I can instantly see the problem.

The formula should be

=COUNTIFS($G6:$AE6,">="&(G6-3),$G6:$AE6,"<="&(G6+3))>1

For some reason, you have

=COUNTIFS($G6:$AE6,">="&(XEM6-3),$G6:$AE6,"<="&(XEM6+3))>1

Following the steps in post 12 should fix it.

 

[cspgsl]

Sorry to be a pain, but I must be doing something wrong as the results are still eluding me.

When I select the range I left click on G6 and drag down to G31 then holding the CTRL key, I do the same for the columns M, S, Y and AE which gives me the range =$G$6:$G$36,$M$6:$M$36,$S$6:$S$36,$Y$6:$Y$36,$AE$6:$AE$36

You can see in the images the formula is correct.

I have added a couple of other rules to cover the cells to the right of the table but I wouldn't expect those to effect what we are trying to accomplish in the main table.