Ask Your Question

Only the first two conditional formats work

asked 2019-12-25 14:37:27 +0100

Inclement gravatar image

updated 2020-08-09 20:12:26 +0100

Alex Kemp gravatar image

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 flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2019-12-25 15:07:38 +0100

Inclement gravatar image

updated 2019-12-25 15:11:46 +0100

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.

edit flag offensive delete link more


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

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

answered 2019-12-25 14:57:35 +0100

Lupp gravatar image

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.

edit flag offensive delete link more


Cross-posting, sorry 😁

Mike Kaganski gravatar imageMike Kaganski ( 2019-12-25 15:03:32 +0100 )edit

answered 2019-12-25 15:02:56 +0100

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".

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-12-25 14:37:27 +0100

Seen: 114 times

Last updated: Dec 25 '19