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