How to extend conditional formatting before appears a new value in Col B?

[Fractalis]

Hello

I have values from A1:D4 like this:
[table="width: 500, class: grid, align: center"]
[tr]
[td][/td]
[td]A[/td]
[td]B[/td]
[td]C[/td]
[td]D[/td]
[/tr]
[tr]
[td]1[/td]
[td]a[/td]
[td]b[/td]
[td]1[/td]
[td]d[/td]
[/tr]
[tr]
[td]2[/td]
[td][/td]
[td][/td]
[td]2[/td]
[td][/td]
[/tr]
[tr]
[td]3[/td]
[td][/td]
[td][/td]
[td]3[/td]
[td][/td]
[/tr]
[tr]
[td]4[/td]
[td]x[/td]
[td]h[/td]
[td]5[/td]
[td]d[/td]
[/tr][/table]

If value on column B="b" I have a conditional formatting to highlight in green the row where "b" is present (in this case in row 1). If value in column B="h" then I have a conditional formatting to highlight in yellow the row where "h" is present (in this case in row 4)

How can I extend the formatting in order that If B1="b" be highlighted automatically in green all range from A1:D3? This is highlight in green all the values in range before a new value appears in column B.

The issue is the number of values in column C could be one or more.

Thanks in advance for any help.

 

[Marcelo Branco]

Try

Select A1:D4

Rule 1
=LOOKUP(2,1/($B$1:$B1<>""),$B$1:$B1)="b"
Fill --> Green

Rule 2
=LOOKUP(2,1/($B$1:$B1<>""),$B$1:$B1)="h"
Fill --> Yellow

Hope this helps

M.

 

[Fractalis]

Try

Select A1:D4

Rule 1
=LOOKUP(2,1/($B$1:$B1<>""),$B$1:$B1)="b"
Fill --> Green

Rule 2
=LOOKUP(2,1/($B$1:$B1<>""),$B$1:$B1)="h"
Fill --> Yellow

Hope this helps

M.

Oi Marcelo,

It works just exactly as I wanted. Muito obrigado.

Regards

 

[Marcelo Branco]

You are welcome. Glad to help.

M.