Format if cell value is equal to AND if adjacent cell value is.

[cssmith]

We are trying to find subcontractors we did not pay for Job 5202, but we got paid for Job 5202.

Column A is Yes or No (Did we get paid for the work order)
Column B is Yes or No (Did we pay subcontractor for that work order)

If B2 is No, I'd like to highlight it green only if A2 is Yes.
(meaning we didn't pay out, but got paid)

If B3 is No, and A3 is No, no formatting needed.
(meaning we haven't been paid yet)

If B4 is Yes and A4 is Yes, no formatting needed.
(meaning we got paid and paid out)


And there is no possibility we paid out if we haven't been paid yet, but if needed for the formula, if B5 is Yes and A5 is No, there is no formatting needed.

 

[njimack]

Conditional formatting formula:
Green: =AND(B2="No",A2="Yes")

 

[igold]

Hi,

What I am reading from your description is that only one instance needs to formatted. In Excel 2010 in the conditional formatting drop down you would use the last rule type listed "Use a formula to determine which cells to format" and that formula would be:

=AND(B2="No",A2="yes")

You can then choose whatever color suits you with the "Format..." button.

HTH

igold

 

[cssmith]

Conditional formatting formula:

Green: =AND(B2="No",A2="Yes")

I was just about to reply to myself, I did do that, but it turns the text in the cell above green. As in, B1 will turn green even though it's the column header.

 

[cssmith]

Hi,

What I am reading from your description is that only one instance needs to formatted. In Excel 2010 in the conditional formatting drop down you would use the last rule type listed "Use a formula to determine which cells to format" and that formula would be:

=AND(B2="No",A2="yes")

You can then choose whatever color suits you with the "Format..." button.


HTH

igold

I must be missing something here. I did try that, but instead of B2 turning green itself if the value is No, B1 will turn green.

A2=Yes and B2=No, but B2 does not format green text, for some reason whatever is in B1 turns green.

 

[igold]

Are you sure you have entered the conditional format into cell B2 and not in B1. Also if you look at the Conditional Format drop down under "Manage Rules" it will show you what cell the format applies to. Make sure it says B2.