Hi.

To do the following, set up the following example:

In cell B3, type: =NOW() and format it to Date (dd/mm/yyyy)
In cell B4, type: 16/01/2005 (this is the user defined date)
In cell B5, type: =DAYS360(B4,B3)
Now set cell B5's colour to Green (select the cell, right click, Cell
Properties, Pattern, and select the Green colour)
Now, while still on B5, select Format Conditional Formatting.
First row should read "Cell Value Is", "less than or equal to", then type 10.
Click Format, and set the pattern to RED.
Now select "Add"
Second row should read "Cell Value Is", "less than or equal to", then type 20.
Click Format, and set the pattern to ORANGE.
Now select "Add"
Second row should read "Cell Value Is", "less than or equal to", then type 30.
Click Format, and set the pattern to YELLOW.

This will work, as it excludes the need to look for a 4th condition (a limit
in Excel Conditional Formatting). If you wanted RED to be the default
colour, change all "less than or equal to" statements to "greater than". The
NOW() statement prevents you needing to type in todays date.


"RRP333" wrote:

Dear Experts,

I would like my users to be able to input a date in a cell, then Excel
automatically calculates the elapsed duration in days from the inputted date
to today's date, and changes the color of the cell to one of four colors,
based on the 3 user-specified durations that correspond to the three colors.
So if the user-specified durations are 10 days (if duration d =< 10, cell
color = red), 20 (if 10< d =<20, cell color = orange), and 30 (if 20< d =<30,
cell color = yellow, and if d30 cell color = green), the cell color will
automatically change based on today's date. Ideally, the user-specified
thresholds can be different for each cell, or at least for each column.

Can this be done with conditional formatting or is code required? I'm new
at both so any help would be greatly appreciated!

Thank you, RRP333