Change cell colour based on multiple values

[SaraWitch]

Hello peeps,

I am trying to change a cell colour if certain text is not selected from DDLs.

So, I have one DDL in A1 and another, different, one in B1. If option "S188" is selected in B1, but either "MDS" or "UR" is not selected in A1 then change cell colour. And if "S193" is selected in B1 but "MDS" is not selected in A1, then change cell colour also.

I'm sure these would only be two conditional format rules (one for "S188" and one for "S193") and I have tried several OR and AND statements but nothing is working. My picklist in A1 is quite long, so I don't want to apply a conditional format for everything but the options as above.

Hope this makes sense for a Friday afternoon!!

Any help would be appreciated, thank you

 

[Oaktree]

Try:

1) Select cell C1 (or whichever cell you want the conditional formatting to apply to)

2) Enter Conditional formatting rule 1 --> Use a formula to determine which cells to format --> use this as the "format values where this formula is true":

=AND(B1="S188",A1<>"MDS",A1<>"UR")

apply desired formatting

Conditional formatting rule 2--> Use a formula to determine which cells to format --> use this as the "format values where this formula is true":

=AND(B1="S193",A1<>"MDS")

apply desired formatting

 

[awoohaw]

try this:

Mr Excel Questions 73.xlsm
	A	B
1
2	NOT MDS or UR	S188
3	NOT MDS	S193
4	MDS	S188
5	UR	S188
6	MDS	S188
7	MDS	DIFFERENT
8	MDS	S188
9	UR	S188
10	MDS
11		S188
12	OTHER	DIFFERENT
SaraWitch

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A12	Expression	=AND(B2="S193",B2<>"MDS")	text	NO
A2:A12	Expression	=AND(B2="S188",OR(A2="MDS",A2="UR")=FALSE)	text	NO

 

[SaraWitch]

Thank you, @Oaktree - that works the other way round. @awoohaw's solution is spot on!! Thank you so very much!

Enjoy the weekend, both

 

[awoohaw]

Happy to help. Best Wishes!