Highlight Adjacent Cells in a grid

JLEMS

New Member
Joined
May 27, 2020
Messages
16
Office Version
365
Platform
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

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,688
Office Version
2007
Platform
Windows
Hi and welcome to MrExcel.

Using your example as a base, create 3 conditional formatting rules:
Rule 1:
Formula:
=AND(COUNTIF($A1:A1,$F$5)=1,A1=$F$5,MATCH($F$6,$A2:$C2,0),MATCH($F$7,$A3:$C3,0))
Applies to:
=$A$1:$C$10

Rule 2:
Formula:
=AND(COUNTIF($A2:A2,$F$6)=1,A2=$F$6,MATCH($F$5,$A1:$C1,0),MATCH($F$7,$A3:$C3,0))
Applies to:
=$A$2:$C$10

Rule 3:
Formula:
=AND(COUNTIF($A3:A3,$F$7)=1,A3=$F$7,MATCH($F$5,$A1:$C1,0),MATCH($F$6,$A2:$C2,0))
Applies to:
=$A$3:$C$10
 

JLEMS

New Member
Joined
May 27, 2020
Messages
16
Office Version
365
Platform
Windows
Thank you, I will try and see what the formula is trying to say. I included a snip of the actual grid I am trying to service. I am also looking to expand it later on into have a 4th number as well. I am also going to need to expand the Applies to area to a few other grids on the chart.
 

Attachments

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,688
Office Version
2007
Platform
Windows
Thank you, I will try and see what the formula is trying to say. I included a snip of the actual grid I am trying to service. I am also looking to expand it later on into have a 4th number as well. I am also going to need to expand the Applies to area to a few other grids on the chart.
Did you try the formulas in the first example?
 

JLEMS

New Member
Joined
May 27, 2020
Messages
16
Office Version
365
Platform
Windows
I did, but they are changing themselves to something different and not highlighting. I was going to try and see why
 

JLEMS

New Member
Joined
May 27, 2020
Messages
16
Office Version
365
Platform
Windows
=AND(COUNTIF($A1048572:XFA1048572,$F$7)=1,XFA1048572=$F$7,MATCH($F$5,$A1048570:$C1048570,0),MATCH($F$6,$A1048571:$C1048571,0))
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,688
Office Version
2007
Platform
Windows
=AND(COUNTIF($A1048572:XFA1048572,$F$7)=1,XFA1048572=$F$7,MATCH($F$5,$A1048570:$C1048570,0),MATCH($F$6,$A1048571:$C1048571,0))
Set this again:
=AND(COUNTIF($A3:A3,$F$7)=1,A3=$F$7,MATCH($F$5,$A1:$C1,0),MATCH($F$6,$A2:$C2,0))

Repeat for the other formulas until the ranges don't change.
 

JLEMS

New Member
Joined
May 27, 2020
Messages
16
Office Version
365
Platform
Windows
It looks like the formula is sticking now. It highlighted a 0 but one 1
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,688
Office Version
2007
Platform
Windows
Check the range to apply, it is different in each formula.

Formula:
=AND(COUNTIF($A1:A1,$F$5)=1,A1=$F$5,MATCH($F$6,$A2:$C2,0),MATCH($F$7,$A3:$C3,0))
Applies to:
=$A$1:$C$10

Rule 2:
Formula:
=AND(COUNTIF($A2:A2,$F$6)=1,A2=$F$6,MATCH($F$5,$A1:$C1,0),MATCH($F$7,$A3:$C3,0))
Applies to:
=$A$2:$C$10

Rule 3:
Formula:
=AND(COUNTIF($A3:A3,$F$7)=1,A3=$F$7,MATCH($F$5,$A1:$C1,0),MATCH($F$6,$A2:$C2,0))
Applies to:
=$A$3:$C$10
 

JLEMS

New Member
Joined
May 27, 2020
Messages
16
Office Version
365
Platform
Windows
It's highlighting, but it's highlighting random numbers
 

Watch MrExcel Video

Forum statistics

Threads
1,098,913
Messages
5,465,414
Members
406,426
Latest member
slari

This Week's Hot Topics

Top