Conditional Formatting Surrounding Cells

[rhogsett]

Hello All,

Thank you in advance for any and all your assistance.

I am having difficulty figuring out how to highlight the surround cells of a cell containing text. I have a spreadsheet where an "X" is entered into various cells within a large range (ie. $C$3:$J$37). I would like to conditionally format the range $C$3:$J$37 so that anytime an "X" is entered within that range, the surrounding cells become filled/highlighted.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]FILL[/TD]
[TD="align: center"]FILL[/TD]
[TD="align: center"]FILL[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]FILL[/TD]
[TD="align: center"]FILL[/TD]
[TD="align: center"]FILL[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]FILL[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]FILL[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]FILL[/TD]
[TD="align: center"]FILL[/TD]
[TD="align: center"]FILL[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]FILL[/TD]
[TD="align: center"]FILL[/TD]
[TD="align: center"]FILL[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

I have spent way to much time trying to figure this out on my own and thought it best to reach out and ask for help.

Any assistance is greatly appreciated!

Thank you,

RG

 

[Joe4]

anytime an "X" is entered within that range, the surrounding cells become filled/highlighted.

How exactly are you defining "surrounding"? From your picture, it looks like you are including columns one column away from the selected cells, but up to 2 rows above and below the selected cell.

 

[63falcondude]

Highlight B2:K38 > Conditional Formatting > New Rule > Use a formula

=OR(A1="X",B1="X",C1="X",A2="X",C2="X",A3="X",B3="X",C3="X")

Format: Fill color of your choice > OK > OK

 

[rhogsett]

63falcondude,

This worked perfectly, thank you so much!

 

[63falcondude]

63falcondude,

This worked perfectly, thank you so much!

You're welcome. Glad to help.

That was a fun one.