Formatting question

[Guest]

I have a spreadsheet where we are capturing customer contact. The first
column contains the date that the call came in, I want to format the table to
make the row green if the date in column 1 is within 5 days from todays date,
to turn amber if it goes over 5 days and red if it goes over 7 days. The
last column will contain the date that the query was completed, when a date
is input into this field I want the row to 'grey' out. Can anyone help????

 

[Guest]

consider using conditional formating. see:


http://www.contextures.com/xlCondFormat01.html

 

[Bob Phillips]

See http://xldynamic.com/source/xld.CF.html#lights

Your formulae would be

=AND($A2<=TODAY(),$A2>TODAY()-5)

=AND($A2<=TODAY(),$A2>TODAY()-7)

=AND($A2<=TODAY(),$A2<>"")

where A2 is the first cell being formatted

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

I have a spreadsheet where we are capturing customer contact. The first
column contains the date that the call came in, I want to format the table to
make the row green if the date in column 1 is within 5 days from todays date,
to turn amber if it goes over 5 days and red if it goes over 7 days. The
last column will contain the date that the query was completed, when a date
is input into this field I want the row to 'grey' out. Can anyone

help????

 

[Guest]

Have tried this but it's not working, probably me I know! However, this will
just change the date cell the required colour - I really want to turn the row
the relevant colour? Any ideas?

 

[Bob Phillips]

So select the whole row before applying the formatting. I allowed for that
by making the column absolute.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Bob, thanks so much for this, it works perfectly. Can I ask one more thing?
Column 5 contains a completion date i.e. when the customer query is signed
off as complete - whilst retaining the current formatting, do you know if
there is a way that I can make the whole row go greay when a date is input in
this cell??

Thanks

 

[Bob Phillips]

Could it be anything other than a date, that is do we have to test for a
date, or just a value?

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

The guys just type in the date when they close the enquiry, so I guess it's
just a value.