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

cssmith

New Member
Joined
Jul 12, 2014
Messages
3
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

Well-known Member
Joined
Jun 17, 2005
Messages
7,761
Conditional formatting formula:
Green: =AND(B2="No",A2="Yes")
 

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,447
Office Version
365, 2010
Platform
Windows
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:
Code:
=AND(B2="No",A2="yes")
You can then choose whatever color suits you with the "Format..." button.

HTH

igold
 

cssmith

New Member
Joined
Jul 12, 2014
Messages
3
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:
Code:
=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

Well-known Member
Joined
Jul 8, 2014
Messages
2,447
Office Version
365, 2010
Platform
Windows
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.
 

Forum statistics

Threads
1,081,991
Messages
5,362,595
Members
400,683
Latest member
LogChief

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top