conditional formatting - how to apply format of an adiacent row in Excel when the cell is empty

I have a row containing, let's say, the following values:

0, empty, empty, 1, empty, 0, 1

I want to apply a conditional formatting so that:

• cell 0: background is RED


• cell 1: background is GREEN


• cell empty: background is equal to the previous cell background

the result should be:

RED, RED, RED, GREEN, GREEN, RED, GREEN

The question is: using conditional formatting, how to apply to a cell the format of a previous cell ?

thank you in advance for any comment

Answer

You need to use a formula for this formatting:

• select the range you want to format


• go to home - conditional formatting - new rule - use a formula to decide which cells to format


• for formula field enter this:
  =INDEX($A$1:A1,MAX(IF($A$1:A1<>"",COLUMN($A$1:A1),"")))=1


• set formatting for cells with 1


• the rule for cells with 0, will be similar, just the end is different:
  =INDEX($A$1:A1,MAX(IF($A$1:A1<>"",COLUMN($A$1:A1),"")))=0