Conditional format based on non-blank previous cell

0
votes

I need to conditionally format cells in a row based on the prior non-blank cell

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

Because B1 is AC, i want C1 and D1 to have a conditional format (changed fill which will match the format in B1). That ends when i get to E1 and the value BW. On row 2 i want to ignore b2 and only format d2.

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

currently i have conditional formatting that looks for the list and can format the individual cell but i don't understand how to create a vlookup type of event in the conditional formatting formula field, nor how to find the first non-blank to check

Edit: added the F and G columns to show that i want C1 and D1 to format but not F1 or F2, just the blank cells after the AC or BC.

2
excelexcel-formulaconditional-formatting
there is an Excel template for Projects that uses conditional formatting in that way; recommend you open that and play with it. The Template is "Gantt project planner" and can be accessed by File-->New menu. - Sam

2 Answers

0
votes

Use Aggregate:

=AND(COLUMN(A:A)>AGGREGATE(15,6,COLUMN($A1:$E1)/(ISNUMBER(MATCH($A1:$E1,{"AC","BC","IC","SC"},0))),1),A1="")
0
votes

This is the wrong approach. The real question is here Copy format of previous cell in conditional formatting as all that is necessary is to copy the format from a previous cell. This way is too complex.