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????

consider using conditional formating. see:


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



--
Gary's Student
gsnu200703


"Philip Drury" wrote:

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????

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

Your formulae would be

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

=AND($A2<=TODAY(),$A2TODAY()-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)

"Philip Drury" wrote in message
...
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????

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" wrote:

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

Your formulae would be

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

=AND($A2<=TODAY(),$A2TODAY()-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)

"Philip Drury" wrote in message
...
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????

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)

"Philip Drury" wrote in message
...
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" wrote:

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

Your formulae would be

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

=AND($A2<=TODAY(),$A2TODAY()-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)

"Philip Drury" wrote in message
...
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????

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" wrote:

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)

"Philip Drury" wrote in message
...
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" wrote:

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

Your formulae would be

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

=AND($A2<=TODAY(),$A2TODAY()-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)

"Philip Drury" wrote in message
...
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????

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)

"Philip Drury" wrote in message
...
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" wrote:

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)

"Philip Drury" wrote in message
...
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" wrote:

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

Your formulae would be

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

=AND($A2<=TODAY(),$A2TODAY()-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)

"Philip Drury" wrote in
message
...
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????

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

"Bob Phillips" wrote:

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)

"Philip Drury" wrote in message
...
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" wrote:

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)

"Philip Drury" wrote in message
...
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" wrote:

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

Your formulae would be

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

=AND($A2<=TODAY(),$A2TODAY()-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)

"Philip Drury" wrote in
message
...
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????