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

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!

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

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
" wrote:
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!

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

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: =$L3A20.
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?

Max wrote:
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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
" wrote:
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!

If data in one column, take date, add 2 days, and turn the ent
 

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
" wrote:
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: =$L3A20.
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?

If data in one column, take date, add 2 days, and turn the ent
 

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.

Max wrote:
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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
" wrote:
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: =$L3A20.
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?

If data in one column, take date, add 2 days, and turn the ent
 

" wrote:
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
Singapore
http://savefile.com/projects/236895
xdemechanik
---

If data in one column, take date, add 2 days, and turn the ent
 

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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
Max wrote:
" wrote:
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
Singapore
http://savefile.com/projects/236895
xdemechanik
---