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

  • Thread starter Thread starter richiecw
  • Start date Start date
R

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!
 
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
 
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?
 
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
 
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.
 
[email protected] said:
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.
Click to expand...

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]

---
 
whoops .. lines:
Cond1: =AND($A20<>"",$E20<>" ",$A20+2<TODAY(),$L20="")
Cond2: =AND($A20<>"",$E20<>" ",$L20<>"",$L20>=$A20)
Click to expand...

should have read as
Cond1: =AND($A3<>"",$E3<>" ",$A3+2<TODAY(),$L3="")
Cond2: =AND($A3<>"",$E3<>" ",$L3<>"",$L3>=$A3)
Click to expand...

(pasted from the wrong row earlier)

---
Max said:
[email protected] said:
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.
Click to expand...

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]

---
Click to expand...
 
Back
Top