Highlight Adjacent Cells in a grid

JLEMS

New Member
Joined
May 27, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows
Hi. I am new here and I have been trying to figure out how to get a spreadsheet to do what I need it to, but have not found the exact way to get it done. I have a 10X10 grid of random numbers. What I want to happen is I want a formula encompassing the entire grid, and highlight only the cells that are adjacent to each other if all 3 numbers are there. For example, I included a smaller scale image. I have a grid of numbers and only want the 312 to highlight, but only if the cells are adjacent to each other. I don't want it to highlight all the 3's 1's and 2's. Only if all 3 are there.

Think of it like a wordsearch puzzle. Does this make any sense and can anyone help?

Thanks
 

Attachments

  • 312.jpg
    312.jpg
    17.5 KB · Views: 9

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
I did have the numbers on a randbetween. I set it to fixed numbers now, it it highlighting, but it isn't highlighting the right numbers
 
Upvote 0
I did have the numbers on a randbetween. I set it to fixed numbers now, it it highlighting, but it isn't highlighting the right numbers
Did you try my test file? so you can see the correct position of the formulas.
 
Upvote 0
I see what you did. It's looking only in the column. Is there a formula for a cell to do the same thing, but check the 8 cells around it, crossing columns?
 
Upvote 0
I see what you did. It's looking only in the column. Is there a formula for a cell to do the same thing, but check the 8 cells around it, crossing columns?

I do not understand what you mean.
The formulas work for your first example, even if the numbers are in another column:

1590594665831.png
 
Upvote 0
@Dante,

Your example table in Message #15 raises an interesting question that the OP will need to address for you. Look at the top highlighted triplet in your example table... there is a second solution that could (should?) be highlighted... instead of the 3 at the top that your formula highlighted, the 1 and 2 could combine with the 3 that touches both of them instead. So, the question the OP has to answer for you is whether there is a preference for which one to choose and, if so, what is the criteria. Or alternately, while the picture would be confusing, should both of the triplets be highlighted (even though that would mean 4 cells would be highlighted. I can also imagined triplets where two of them shared a common cell (or two). For example if the 6 under the 2 in your top highlighted triplet were a 3 instead, then you would have two triplets with that 2 in common... should both of those be highlighted even though that would produce a longish snake-like highlight of 5 cells. I can imagine other triplet neighbors that would also need the OP's attention as well.
 
Upvote 0
Your example table in Message #15 raises an interesting question that the OP

I'm only taking the first set of "triplets" because in the first OP example He only highlighted one cell:
1590596405089.png

I don't know if the OP didn't notice that there is another "2" in the same row (cell C5), I need him to explain what the rule would be.

_________________________________________________________________________________________________________________________________
Meanwhile, these would be the formulas for a matrix as shown in the figure below.

=AND(COUNTIF($B2:B2,$N$5)=1,B2=$N$5,MATCH($N$6,$B3:$L3,0),MATCH($N$7,$B4:$L4,0))
=AND(COUNTIF($B3:B3,$N$6)=1,B3=$N$6,MATCH($N$5,$B2:$L2,0),MATCH($N$7,$B4:$L4,0))
=AND(COUNTIF($B4:B4,$N$7)=1,B4=$N$7,MATCH($N$5,$B2:$L2,0),MATCH($N$6,$B3:$L3,0))

Applies to respectively:
=$B$2:$L$11
=$B$3:$L$11
=$B$4:$L$11



1590596296714.png
 
Upvote 0
That last example is what I am looking for, however, I only want the cells to highlight if cells containing 312 connect with each other. I also see 2 sets of 31 with a 2 in the corner that it is not highlighting
 
Upvote 0
That last example is what I am looking for, however, I only want the cells to highlight if cells containing 312 connect with each other. I also see 2 sets of 31 with a 2 in the corner that it is not highlighting
You could clarify it for me with an image.
 
Upvote 0
In the following image, which ones should be highlighted?

1590597957729.png
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top