We have a spreadsheet with closing dates on it. We'd like the closing date
field's pattern to turn red when today after the closing date.

BUT, once a date is input into another field along the same row, we'd like
the first field to revert to its original colour.

Can anyone help please?

Use conditional formatting with a formula of

=AND(B2="",A2<TODAY())

where A2 is the closing date, B2 is the other field.

--
HTH

Bob

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

"bollard" wrote in message
...
We have a spreadsheet with closing dates on it. We'd like the closing date
field's pattern to turn red when today after the closing date.

BUT, once a date is input into another field along the same row, we'd like
the first field to revert to its original colour.

Can anyone help please?

Hi Bob

Thanks for that, it works a treat.

Only thing is, if the closing date field is empty, then the field also turns
red. Is there a way around this, so that the field only turns red if there is
a closing date and it has expired?

Thanks.

"Bob Phillips" wrote:

Use conditional formatting with a formula of

=AND(B2="",A2<TODAY())

where A2 is the closing date, B2 is the other field.

--
HTH

Bob

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

"bollard" wrote in message
...
We have a spreadsheet with closing dates on it. We'd like the closing date
field's pattern to turn red when today after the closing date.

BUT, once a date is input into another field along the same row, we'd like
the first field to revert to its original colour.

Can anyone help please?

=AND(A2<"",B2="",A2<TODAY())


--
HTH

Bob

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

"bollard" wrote in message
...
Hi Bob

Thanks for that, it works a treat.

Only thing is, if the closing date field is empty, then the field also
turns
red. Is there a way around this, so that the field only turns red if there
is
a closing date and it has expired?

Thanks.

"Bob Phillips" wrote:

Use conditional formatting with a formula of

=AND(B2="",A2<TODAY())

where A2 is the closing date, B2 is the other field.

--
HTH

Bob

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

"bollard" wrote in message
...
We have a spreadsheet with closing dates on it. We'd like the closing
date
field's pattern to turn red when today after the closing date.

BUT, once a date is input into another field along the same row, we'd
like
the first field to revert to its original colour.

Can anyone help please?

Thanks Bob.

Now that really has done the trick. many thanks.

I've got another similar problem now:

We have a person's name coloured according to the month they start work.
We'd like that colour to revert to pale grey once we enter a date in another
field on the same row.

This sounds like the same issue, but I tried using one of your earlier
formulae and that doesn't do it.

Thanks.

"Bob Phillips" wrote:

=AND(A2<"",B2="",A2<TODAY())


--
HTH

Bob

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

"bollard" wrote in message
...
Hi Bob

Thanks for that, it works a treat.

Only thing is, if the closing date field is empty, then the field also
turns
red. Is there a way around this, so that the field only turns red if there
is
a closing date and it has expired?

Thanks.

"Bob Phillips" wrote:

Use conditional formatting with a formula of

=AND(B2="",A2<TODAY())

where A2 is the closing date, B2 is the other field.

--
HTH

Bob

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

"bollard" wrote in message
...
We have a spreadsheet with closing dates on it. We'd like the closing
date
field's pattern to turn red when today after the closing date.

BUT, once a date is input into another field along the same row, we'd
like
the first field to revert to its original colour.

Can anyone help please?

I assume that you use normal cell colouring for the month colouring, as CF
cannot support 12?

So just add a CF formula of

=J2<""

as an example, and format as grey

--
HTH

Bob

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

"bollard" wrote in message
...
Thanks Bob.

Now that really has done the trick. many thanks.

I've got another similar problem now:

We have a person's name coloured according to the month they start work.
We'd like that colour to revert to pale grey once we enter a date in
another
field on the same row.

This sounds like the same issue, but I tried using one of your earlier
formulae and that doesn't do it.

Thanks.

"Bob Phillips" wrote:

=AND(A2<"",B2="",A2<TODAY())


--
HTH

Bob

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

"bollard" wrote in message
...
Hi Bob

Thanks for that, it works a treat.

Only thing is, if the closing date field is empty, then the field also
turns
red. Is there a way around this, so that the field only turns red if
there
is
a closing date and it has expired?

Thanks.

"Bob Phillips" wrote:

Use conditional formatting with a formula of

=AND(B2="",A2<TODAY())

where A2 is the closing date, B2 is the other field.

--
HTH

Bob

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

"bollard" wrote in message
...
We have a spreadsheet with closing dates on it. We'd like the
closing
date
field's pattern to turn red when today after the closing date.

BUT, once a date is input into another field along the same row,
we'd
like
the first field to revert to its original colour.

Can anyone help please?

Hi Bob

Many thanks for that.

"Bob Phillips" wrote:

I assume that you use normal cell colouring for the month colouring, as CF
cannot support 12?

So just add a CF formula of

=J2<""

as an example, and format as grey

--
HTH

Bob

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

"bollard" wrote in message
...
Thanks Bob.

Now that really has done the trick. many thanks.

I've got another similar problem now:

We have a person's name coloured according to the month they start work.
We'd like that colour to revert to pale grey once we enter a date in
another
field on the same row.

This sounds like the same issue, but I tried using one of your earlier
formulae and that doesn't do it.

Thanks.

"Bob Phillips" wrote:

=AND(A2<"",B2="",A2<TODAY())


--
HTH

Bob

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

"bollard" wrote in message
...
Hi Bob

Thanks for that, it works a treat.

Only thing is, if the closing date field is empty, then the field also
turns
red. Is there a way around this, so that the field only turns red if
there
is
a closing date and it has expired?

Thanks.

"Bob Phillips" wrote:

Use conditional formatting with a formula of

=AND(B2="",A2<TODAY())

where A2 is the closing date, B2 is the other field.

--
HTH

Bob

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

"bollard" wrote in message
...
We have a spreadsheet with closing dates on it. We'd like the
closing
date
field's pattern to turn red when today after the closing date.

BUT, once a date is input into another field along the same row,
we'd
like
the first field to revert to its original colour.

Can anyone help please?