Excel: Conditional formatting on merged cells with an offset

Question

I am trying to use conditional formatting in my merged cells. In the screenshot I merged B3:B5.

If a Cell contains the word "Apfel", I would like to format this cell and the following 3 cells (so B3 = "Apfel" ==> B3:E3 should be green).

If I try to use the conditional formatting rule [=$B3="Apfel"] an I select my desired area [Cell that contains "Apfel" ], it works. But If I merge B3 with B4 and B5, it only works for B3:E3 and not for B3:E5.

So I am trying to work with an OFFSET, but I get an Error message.

I tried [=OFFSET($C3,0,-1)="Apfel"], but it gives me a syntax error.

What am I doing wrong?

Answer 1

select everything from b3 to e9 and then conditional formatting new rule-
=$B3="apfel" 
Try this.