Conditional Formatting - more than 2 merged rows

[blandest]

Hi,
I found partial solution for my problem in an older thread. I need to apply conditional formatting from merged cell to unmerged cells.
It works for 2 merged rows, but I would need to apply formatting to 4 rows. For 4 merged rows it's applied only to the first 2 rows.
If there is a text (x) in a B column, then formatting is applied to the rest of the row in the table.
Check the xl2bb.

Test plan HZB_EN_DE_test.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
2	x	text
3
4	x	text
5
6	x	text
7
8	x	text
9
10
11	x	text
12
13
14
15	x	text
16
17
18
19	x	text
20
21
22
23	x	text
24
25
26
Tests

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:S9,B11:S26	Expression	=OR(ISTEXT($B1),ISTEXT($B2))	text	NO

 

[jdellasala]

Welcome to the problem with Merging cells. Just don't do it! Use "Center Across Selection" under Horizontal Alignment.

That way your columns don't get all screwed up!

 

[blandest]

Welcome...

Thanks for the hint! I already noticed, that there is a lot of fun with merged cells
Unfortunately, this doesn't solve my problem.
There is no such option under Vertical alignment, which I would need. And even if this option would be there I'm not sure if it would format all the rows behind it.
My goal is to apply formatting to columns after column B if there is a text in column B.

 

[excel_learnerz]

To apply conditional formatting to a merged cell and the cells below it, you can modify the formula in the conditional formatting rule to check if either the merged cell or the cell directly below it contains the specified text.

For example, to apply conditional formatting to a merged cell spanning four rows, you can use the following formula:

=OR(ISTEXT($B1),ISTEXT($B2),ISTEXT($B3),ISTEXT($B4))

This formula checks if any of the four cells in column B (B1, B2, B3, or B4) contains text. If any of these cells contain text, the conditional formatting will be applied to the entire row.

To apply this formula to the merged cells B2:S9 and B11:S26, you can select these cells, open the conditional formatting dialog, and create a new rule with the "Use a formula to determine which cells to format" option. Then, enter the above formula in the formula field, choose the desired format, and click OK to apply the rule.

Note that you should uncheck the "Stop If True" option, as this will prevent the formatting from being applied to subsequent rows if the formula evaluates to TRUE in the first row.

 

[blandest]

To apply...

I got it!
I already tried adding other cells to the formula (B3, B4), but it was not working.
The catch is that you have to add previous cells there. But sice you cannot add B0, B-1 you have to move the table down so the table begins on B6 (or whatever) and then the formula looks like this =OR(ISTEXT($B3),ISTEXT($B4),ISTEXT($B5),ISTEXT($B6)) with the table beginning on B6. It makes no sense, but it works Check the xl2bb

Test plan HZB_EN_DE_test.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
6	x	text
7
8
9
10	x	text
11
12
13
14		text
15
16
17
18	x	text
19
20
21
Tests

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B6:S21	Expression	=OR(ISTEXT($B3),ISTEXT($B4),ISTEXT($B5),ISTEXT($B6))	text	NO