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]

How about

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

as a conditional format rule applied to $G$6:$AE$36

Probably not the most efficient method, but should be the easiest one to set up.

 

[cspgsl]

Thanks Jason. As I said, I am not very adept at the more powerful features of Excel. It's never been a big part of my life.

Can you give me the steps I would need to take to add this? Do I have to name a range of columns Daily Yardage?

 

[jasonb75]

Sure, no named range needed, the formula will look at the column headings.

Start by selecting the range to highlight with your mouse by clicking on G6, then holding the left mouse button and dragging to AE36.

Next, select 'Conditional Formatting' on the excel 'Home' tab, then from the options shown, choose 'New Rule'

From the options in the new box, select 'Use a formula to determine which cells to format.

Enter the formula from post 2 into the empty box.

Click the 'Format' button, then choose your highlight colour on the 'Fill' tab.

Click OK twice to finish.

 

[cspgsl]

I think I created it correctly but am not getting any returns

 

[jasonb75]

Not sure why you have 4 rules in the bottom right image, there should only be 1.

Try deleting the extra rules, you only need the top one.

 

[cspgsl]

sorry, same result

 

[jasonb75]

As far as I can see, you've done everything correctly. I've tested the method with the daily yardage figures from post 1 and the results match your example, with the exception that it also picks up G7 (164) and AE7 (163) as a match. With this in mind, I can only assume that there is something in your data that is not matching up correctly.

Is the data imported from an external source, for example, a web page?

In some cases numbers can be imported as text with spaces / hidden characters which can cause formulas to fail.

If you enter =COUNT(G6:G36) into the worksheet, does it return a result of 31? Try the same for M6:M36, etc each one should return 31, any less indicates a problem with the numbers in that column.

 

[cspgsl]

No, the data isn't imported. I ran your formula and all 5 columns returned 31

Would formulas in the cells make a difference to your suggestion?

 

[jasonb75]

The only other thing that I can think of is stray spaces in the column headers casuing the formula to not recognise 'Daily Yardage' correctly.
I used that to try and simplify the process, but it looks like that idea was counter productive

Try changing the formula to

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

and the 'Applies to:-' range to

=$G$6:$G$36,$M$6:$M$36,$S$6:$S$36,$Y$6:$Y$36,$AE$6:$AE$36

hopefully it will work this time.