Advanced Conditional Formatting or Macro?

Question

**UPDATE: I was able to use the AND function:

=AND(C3=A5,E5="") format color red =AND(C3=A5,E5>12/11/2013) format color green

The only downside is that I have to manually enter this rule over and over with the new cell location. Also as long as the higher number is in the rule, it is able to tell which rule to use.

IE:

=AND(C3=A6,E6="") format color red
=AND(C3=A6,E6>12/11/2013) format color green
=AND(C3=A5,E5="") format color red
=AND(C3=A5,E5>12/11/2013) format color green

I have a worksheet that we have to track items on. These items can be taken out and then returned.

Example:

O-Scope Available:  Oscope #1   Oscope #2   Oscope #3   Oscope #4   Oscope #5

Serial/Kit No.: Assigned To:    Date Out:   Expected Return:    Returned on:

When Oscope #1 is selected in "Serial/Kit No." column and has no "Returned on" date in that column, I want that cell to turn red to show it is not in the building. When Oscope #1 is selected in "Serial/Kit No." and a return date is entered then I want the cell to turn green, to show it is available.

This is an ongoing list that we need to track and the highlighting should be continous. IE if Oscope #1 was checked out and returned and someone else is going to check it out of the building, the formatting/marco will recognize this new entry and apply the correct color.

I have tried conditional formatting but it will only work for one row and not the entire worksheet. I used "IF" formula

Thank you

I have all the OScopes highlighted in red and the conditional formatting is turn it green.

Formula: =IF(C3=A5,E3>12/12/2012) Formats Green fill in cell

I originally had ...C3=A5:A66,E3:E66>... but it did not work to recognize the new data

Here is a screenshot

What happens if you copy the formula you tried on all cells of the column? Can you share the Formula text? — Ahmad
@user3087820 You can post/edit the raw link to your image. Usually other users will convert it to a normal picture for you — nixda
FYI: yoursumbuddy.com/no-ifs-in-conditional-formatting-formulas — Doug Glancy
I tried the AND function, thank you Doug. But this did not work: =AND(C3=A5, E5>12/1/2013) it did not change the color. In addition how would you make the formula include the entire column and it only use the newest info? — Gladys

Doug Glancy Doug Glancy · Accepted Answer · 2013-12-10T23:19:02

I don't have much time, but here's an alternative approach. The results look like this:

enter image description here

The Conditional Formatting formula is:

=COUNTIFS($A$3:$A$5,C$1,$E$3:$E$5,"")

It's just checking whether there is any row where the Oscope name is in column A and column E is blank.

Advanced Conditional Formatting or Macro?

1 Answers