Conditional formatting to match other cells depending on "text"

[Lmaonade]

Hi,

I have a sheet I'm having trouble trying to make work without macros. I've pasted below an example of part of my sheet.

I already have conditional formatting set up a cell to change colour if it has the letter in cells A,C,E,G, etc. How do I set up a rule for the cell directly to the right to match those cell and the cell directly underneath to also match the cell.

So basically, if cell A3 = "C" I want cell B3 and A4 to be formatted to change to a specific colour. I want these rules across my entire sheet, without having to create a lot of different rules. I'd appreciate any help you can give me.

Kind regards,
Andy

Rota New (2022).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1					C	Long Day			C	Long Day	C	Long Day	C	Early Day
2					Oldham				Meadows		Oldham		Oldham
3	C	Night	C	Night	C	Night			C	Night	C	Night	C	Night
4	Rochdale		Rochdale		Bury				Rochdale		Rochdale		Bury
5					C	Night	C	Night	C	Night
6					Bury		Bury		Bury
7	C	Night	C	Night							C	Night
8	Stockport		Tameside								Tameside
9					C	Night	C	Night					C	Night
10					Liverpool		Liverpool						Stockport
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:N10	Cell Value	="A"	text	NO
A1:N10	Cell Value	="B"	text	NO
A1:N10	Cell Value	="W"	text	NO
A1:N10	Cell Value	="C"	text	NO

 

[6StringJazzer]

It is possible to do this with a single rule for each color, assuming your format is exactly like what you are showing in your example. Use this rule to replace the one you have.

For blue:
Applies To: A1:N10
New rule > Use a formula to determine which cells to format:

=OR(A1="A",IF(ROW(A1)>1,OFFSET(A1,-1,0)="A"),IF(COLUMN(A1)>1,OFFSET(A1,0,-1)="A"))

Duplicate for other colors, updating "A" to "B", "W", etc.

I don't think the merged cells are going to be a problem in this particular case but Excel experts generally discourage the use of merged cells.

 

[Lmaonade]

It is possible to do this with a single rule for each color, assuming your format is exactly like what you are showing in your example. Use this rule to replace the one you have.

For blue:
Applies To: A1:N10
New rule > Use a formula to determine which cells to format:

=OR(A1="A",IF(ROW(A1)>1,OFFSET(A1,-1,0)="A"),IF(COLUMN(A1)>1,OFFSET(A1,0,-1)="A"))

Duplicate for other colors, updating "A" to "B", "W", etc.

I don't think the merged cells are going to be a problem in this particular case but Excel experts generally discourage the use of merged cells.

Hi 6StringJazzer.

This works perfectly. I really appreciate your help. Thank you so much.

Kind regards,
Andy

 

[6StringJazzer]

Glad to help!