Can someone help me with a work around for this conditional formatting roadblock?

jasmine.pena

When using Conditional Formatting, I want to make my primary column change colors when a combination of choices are made in a multi-select dropdown.

I have it set up to apply this format when the condition is not met: If column 2 is 'condition' and column 3 contains 'condition one' OR 'condition two' then apply this format to column one

Is there a way to change it to column 3 contains 'condition one' AND 'condition two'?

Multiple conditional formatting rules do not work since the higher rules take priority which blocks the functionality I am looking for. Additionally and unfortunately the "define custom criteria" workaround does not work for this situation as there isn't an option for "Does not contain" and only an option for "contains"

Am I missing something to make this work, or can someone help me with a workaround for this conditional formatting roadblock? Is there another option I can use?

acline

To better help you with this problem, could you give more information about what the conditions are and what formatting you want? I will set up a sheet so I can do some testing.

You can sometimes get a lot of added functionality by creating extra columns. Put the criteria in the column as a formula, and then format the rows based on what is in the column.

E.g. If you have a number column, use

=IF(some_column > 10, 1, 0)

This will put a 1 in that cell if some_column is greater than 10, otherwise, it will be 0.

Then do conditional formatting to format the rows/cells based on some_column having or not having a 1 or 0.

You can also make it a checkmark column so it looks nicer, or you can hide it. I would put it at the end to make copying and pasting easier.

jasmine.pena

@acline: Here is a screenshot of the sheet that I am working with and the current formatting rule I am trying to establish. Please let me know if this gives you everything you need or if I need to provide additional information.

If "Type of People Change" is "Reorg/Restructure" and the "With whom have you already consulted" column does not contain both "CFO" AND "P&O" from the drop-down then I need the "With whom have you already consulted" cell at that row to turn red.

If I can get this to work then I will have 32 additional conditional formattings to set up that follow this same style.