excel365 - Conditionally format rows based on two string values?

Question

Excel noob here. Lets say I have a sheet.

.	A	B	C	D
1	Adam	3	No	No
2	Betty	13	Yes	No
3	Chris	12	No	Yes
4	Dave	0	Yes	Yes
5	Emma	1		No

I want to conditionally highlight cells in multiple ways:

• if column C and column D both contain the word 'Yes' colour the row green (e.g. match row 4)
• if column C and column D are not the same, colour the row yellow (e.g. match rows 2,3)
• if column C or D are empty, colour the row red (e.g. match row 5)

I set up two worksheet conditional formatters

1. =AND(SEARCH("Yes",$C2)>0,SEARCH("Yes",$D2)>0) is set to be green
2. =$C2<>$D2 is set to be yellow
3. =OR(ISBLANK($C2),ISBLANK($D2)) is set to be red

However, I'm getting mixed results. I get green rows where C and D are No or where C and D are different. I don't get any formatting for the second or third rules.

I tried just matching column values directly; =$C2="Yes" doesn't match, hence resorting to SEARCH.

Answer 1

Your observed behaviour is because of the commonality between rules 2 & 3, i.e. if C2 differs from D2 then it is, superficially, because the content of those cells differ but, if one of the cells is blank, when the other isn't, then both rules 2 & 3 are satisfied simultaneously.

In the screenshot below, I have made the assumption that 'half-blank' rows are to be identified before 'non-equal' rows: the formula for 'equal' rows is

=AND(COUNTIF($C1,"*Yes*"),COUNTIF($D1,"*Yes*"))

(COUNTIF() is used because it caters for Yes being found within a longer string of text and, if not found at all, will return 0, which is interpreted, inside the AND() function, as FALSE, whereas if Yes is found then the function will return 1, interpreted as TRUE)

Given the non-independence of your rules 2 & 3 it is important that the rules implemented are prioritised as illustrated in the screenshot, which can be achieved by using the up and down buttons (highlighted) to move the currently-selected rule either up or down.