If data in one column, take date, add 2 days, and turn the entire column a color...

[richiecw]

I have two columns setup... Column A would have a received date and
Column E would have a name. What I'm looking to do is if there is a
name filled in in Column E (any data at all), then take Column A and
add two days. If it's past due (example: Column A has 8/01/06, today is
8/22/06, so it would be past due), then turn the entire row a selected
color.

Is this possible? Thanks!

 

[Guest]

Select entire sheet (A1 active)
Click Format > Conditional Formatting
Under Condition 1, make it as:
Formula is: =AND($A1<>"",$E1<>"",$A1<TODAY())
Format the trigger fill color to taste > OK out

 

[richiecw]

Max,

That didn't seem to fix it. I should mention I also have a conditional
formatting condition set for Column L with the formula of: =$L3>A20.
What it's supposed to do is if there is ANY text in column L, it turns
the whole row a certain color.

So what I'm looking for is a second condition to do what I described,
and turn the row another color. How would I incorporate both formulas?

 

[Guest]

Not very sure sorry, but let's try this on a spare copy ..

Assume the target range is A3:L20
(Remove any CF within A3:L20 first)

Select A3:L20 (with A3 active)
Apply the CF settings as:

Cond1:
=AND($A3<>"",$E3<>"",$A3+2<TODAY(),$L3="")
Format yellow fill

Cond2:
=AND($A3<>"",$E3<>"",$L3<>"",$L3>=$A3)
Format green Fill

 

[richiecw]

Max,

This doesn't seem to work for the dropdown (list) in column E. If
there's a date that's more than 2 days older than today, it turns the
column yellow, however, if you then go back and select an entry in one
of the column E's listbox, it doesn't turn the line back to the normal
white.. it just stays yellow.

 

[Guest]

This doesn't seem to work for the dropdown (list) in column E. If
there's a date that's more than 2 days older than today, it turns the
column yellow, however, if you then go back and select an entry in one
of the column E's listbox, it doesn't turn the line back to the normal
white.. it just stays yellow.

If you go back and *clear* the cell in col E using the Delete key, instead
of selecting the top line "space" from the DV droplist in col E, then CF will
work as expected -- the row will turn "white". This would be the simpler
route.

If you want it to work by selecting the top line "space" from the DV
droplist in col E, then adjust the CF conditions to:

Cond1: =AND($A20<>"",$E20<>" ",$A20+2<TODAY(),$L20="")
Cond2: =AND($A20<>"",$E20<>" ",$L20<>"",$L20>=$A20)

[I copied the "space" exactly from col E's DV source box and pasted it
within the "" in the CFs' former $E3<>"" part]

---

 

[Max]

whoops .. lines:

Cond1: =AND($A20<>"",$E20<>" ",$A20+2<TODAY(),$L20="")
Cond2: =AND($A20<>"",$E20<>" ",$L20<>"",$L20>=$A20)

should have read as

Cond1: =AND($A3<>"",$E3<>" ",$A3+2<TODAY(),$L3="")
Cond2: =AND($A3<>"",$E3<>" ",$L3<>"",$L3>=$A3)

(pasted from the wrong row earlier)

---

This doesn't seem to work for the dropdown (list) in column E. If
there's a date that's more than 2 days older than today, it turns the
column yellow, however, if you then go back and select an entry in one
of the column E's listbox, it doesn't turn the line back to the normal
white.. it just stays yellow.

If you go back and *clear* the cell in col E using the Delete key, instead
of selecting the top line "space" from the DV droplist in col E, then CF will
work as expected -- the row will turn "white". This would be the simpler
route.

If you want it to work by selecting the top line "space" from the DV
droplist in col E, then adjust the CF conditions to:

Cond1: =AND($A20<>"",$E20<>" ",$A20+2<TODAY(),$L20="")
Cond2: =AND($A20<>"",$E20<>" ",$L20<>"",$L20>=$A20)

[I copied the "space" exactly from col E's DV source box and pasted it
within the "" in the CFs' former $E3<>"" part]

---