Copy format of previous cell in conditional formatting

Question

My original question was Conditional format based on non-blank previous cell but i now realize that it was the wrong way to go. I don't need to format based on what the prior value was, just if the cell is blank copy the format from the previous cell.

I need to conditionally format a blank cell based on the previous cell

A    B    C    D    E    F    G
     AC             BW        IN
BW        BC        ST        IN

B1 has a conditional format based on the value AC. C1 is blank so it should get the B1 format. D1 is blank and should get the C1 format. E1 is nonblank so it gets whatever the conditional format is.

The values in that will turn the formatting on come from a list of values i.e. AC, BC, IC, SC.

What i don't understand is how to copy, in the formula itself, the value of the previous format.

I would agree, but the one who makes the decision doesn't. Sure i can format paint, but it's a large sheet and just takes too much time. — cryptoref

barry houdini barry houdini · Accepted Answer · 2017-10-15T15:34:00

You can use a formula in conditional formatting that looks for the previous non blank value, e.g. if the cell should be formatted if it's equal to one of AC, BC, IC, SC....or if it's blank and the previous value is in that list

Use Name Manager to define a name for your list of values e.g. make

List ={AC","BC","IC","SC"}

Now use the following formula in conditional formatting for a range starting at A1

=MATCH(LOOKUP("zzz",$A1:A1),List,0)

This works for text values

Copy format of previous cell in conditional formatting

1 Answers