Conditional Formatting based on Two cells Values/Text

[justin_n84]

I am trying to format a cell based on the two cells before it containing YES or NO.

Example, I would like cell A3 to turn green if cell A1 & B1 both contain YES. If A1 contain YES and B1 contains NO then I'd like them to turn red. If neither of these conditions are met then I would like the cell to stay uncoloured.

Once I have done this I would like to be able to drag the condition down so it does the same for cells A2-C2, A3-C3 and so on....

Please Help!!!

 

[AliGW]

You need two rules. Select columns A and B in their entirety, then create these two rules:

=AND($A1="YES",$B1="NO") - formatted red

=AND($A1="YES",$B1="YES") - formatted green

Done.

 

[justin_n84]

Hi, Thanks for the reply.

Could you explain how you carry this out as I have done the above and it doesn't work. Thanks in advance

 

[AliGW]

What do you mean exactly by "it doesn't work"?

Firstly, get rid of any attempts you have made at setting the rule, as they could interfere.

Next select columns A and B by SHIFT-clicking the column headers. With both columns selected, open the CF dialog and create the two rules I have mentioned using the formula option. Do not ignore the dollar signs: if you do, they won't work! Apply your colour formatting in the CF dialog, then save and exit. Now test any pair of cells in the column and you will see the CF kicking in.

 

[justin_n84]

For some reason selecting the 2 columns in their entirety didn't work properly. I've done the same thing but just highlight the two cells and then dragged the formatting. That has worked well now. Thanks