We have a spreadsheet containing closing dates. We'd like the closing date
field on a row to turn red once the closing date has passed (i.e. today is
later than the closing date).

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

Can anyone help please?

if your closing date is in column A and the reference date is B
select column A and format-conditional format
formula is
=and(today()A1,B1="")
set the format how you want it.
occassionally I have to go back into the equation and delete quote marks
around the formula in which case I have to paste the new format overe the
rest of the column

"bollard" wrote:

We have a spreadsheet containing closing dates. We'd like the closing date
field on a row to turn red once the closing date has passed (i.e. today is
later than the closing date).

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

Can anyone help please?

Use (Format==)Conditional Formatting:

Somthing like .....

e.g Dates in Column A

Highlight column A

CF

FormulaIs: =AND($A1<"",$A1<TODAY(),$B1<1)

Set pattern colour

If column B < 1 cell is coloured and reverts to normal if cell in column B
is 1

HTH

"bollard" wrote:

We have a spreadsheet containing closing dates. We'd like the closing date
field on a row to turn red once the closing date has passed (i.e. today is
later than the closing date).

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

Can anyone help please?

Many thanks for that.

"bj" wrote:

if your closing date is in column A and the reference date is B
select column A and format-conditional format
formula is
=and(today()A1,B1="")
set the format how you want it.
occassionally I have to go back into the equation and delete quote marks
around the formula in which case I have to paste the new format overe the
rest of the column

"bollard" wrote:

We have a spreadsheet containing closing dates. We'd like the closing date
field on a row to turn red once the closing date has passed (i.e. today is
later than the closing date).

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

Can anyone help please?

many thanks for that.

"Toppers" wrote:

Use (Format==)Conditional Formatting:

Somthing like .....

e.g Dates in Column A

Highlight column A

CF

FormulaIs: =AND($A1<"",$A1<TODAY(),$B1<1)

Set pattern colour

If column B < 1 cell is coloured and reverts to normal if cell in column B
is 1

HTH

"bollard" wrote:

We have a spreadsheet containing closing dates. We'd like the closing date
field on a row to turn red once the closing date has passed (i.e. today is
later than the closing date).

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

Can anyone help please?