Link to home
Start Free TrialLog in
Avatar of bowemc
bowemc

asked on

RAG conditional formatting based on relative date

Hi

in 2007 how can I do RAG congitional formatting based on the number of days from today? e.g. say the value of a cell is <3 days from now it should have a red background, 5-3 should be green and >5 is white?

thanks
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

You can use the Formula option for CF using the TODAY() function. I could give an example in Excel 2003 format as working at a 2003 machine but not at a 2007 machine until this evening.

Thanks
Rob H
ASKER CERTIFIED SOLUTION
Avatar of Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bowemc
bowemc

ASKER

how can i easily copy the formatting from one cell to another. I don't want to have to manually set-it all up. Shout the format paintbrush work reliably?
If you use relative cell referencing, as I have given above, you can just copy the cell and use Paste Special > Formats

Or, select the whole table when defining the conditional format and make sure the referencing applies to the current cell, i.e. don't use $A$1 but A1. That will format the whole table at once.

Or, set the conditional format for the one cell, using relative referencing. Then, use the Conditional Formatting > Manage Rules dialog to specify the range that the rule shall be applied to.

Many paths lead to Rome.

It depends on what you are comfortable with. If in doubt, post a sample file and I'll be happy to show what I mean, applied to your file.

cheers, teylyn