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

[Fractalis]

Hello

I have values from A1:D4 like this:

	A	B	C	D
1	a	b	1	d
2			2
3			3
4	x	h	5	d

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.