Only the first two conditional formats work

Simplified example:

Column A can contain two values: "back" (font color red) or "front" (font color blue).

Column B can contain two values: "up" (font color green) or "down" (font color gray).

Column C can contain any combination of those, via concatenation. (back-up, back-down, front-up, front-down)

A           B             C
back      down       back-down
back      up           back-up
front      up           front-up
back      down       back-down
front      down       front-down


I want to use conditional formatting in column C to indicate both states at once. For instance, "back-up" in column C would have font color red and background color green. "Front-down" would have font color blue, background color gray.

The necessary four conditions are all created, but only the first two in the list ever work (whichever ones I move into the top two spots). How can I get the whole list to work?

My conditions look like this:

Condition 1
Formula is A1="back"
Apply Style: back      [font color red]

Condition 2
Formula is A1="front"
Apply Style: front      [font color blue]

Condition 3
Formula is B1="up"
Apply Style: up          [background color green]

Condition 4
Formula is B1="down"
Apply Style: down      [background color gray]

Range: C1:C50

edit retag close merge delete

Sort by » oldest newest most voted

Oh - ack - never mind - I was thinking about it all wrong in the first place. It doesn't go down the list and apply each one in order, it only applies the first one that matches.

So the solution is to use an AND formula in the condition, and create four separate styles:

Condition 1
Formula is AND(A1="back",B1="up")
Apply Style: back-up          [font color red, background color green]


and so forth for the other three.

more

And I see two other helpful people answered while I was writing this - thank you, and sorry for the cross-posting.

( 2019-12-25 15:10:20 +0100 )edit

Only the first condition coming out TRUE is regarded. The evaluation of conditions is exited then.

To get what you want, you need to also define cell styles for all the combinations of attributes you want to be overlaid if a respective combination of conditions is TRUE. Any such combined condition must get assigned the respective style by an extra case in the CF manager.

Be sure to regard what follows from what I already said above:
The most narrowing conditions must be listed first. If a less restricting condition returns TRUE no more restricting condition listed below can put something in force, because it will not be evaluated then.

more

Cross-posting, sorry 😁

( 2019-12-25 15:03:32 +0100 )edit

Conditional formatting always works this way:

If condition 1 is true
Then apply format 1
Else if condition 2 is true
Then apply format 2
Else ...


Note the "Else" part. Only when previous conditions were false, following conditions are tested, and relevant format applied. If you need complex formatting like in your description, you will need four conditions like "value = back-down".

more