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


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

enter image description here