cell colour change when set markers are reached

[balding rapidly!]

i need to get a cell to change colour when markers are reached eg
a qualification lasts 12 months. what i want to do is have the cell change
from yellow to orange to red as the expiry date gets closer.

 

[Stefi]

If column A contains expiry dates then select column A,
Formats>Conditional Formatting>formula1:
=DATEDIF(TODAY(),A1,"m")<1 red for 1 month
Click Add button, formula2:
=DATEDIF(TODAY(),A1,"m")<2 orange for 2 month
Click Add button, formula3:
=DATEDIF(TODAY(),A1,"m")<3 yellow for 3 month

Adjust number of months as you like!

Regards,
Stefi

„balding rapidly!†ezt írta:

 

[Shane Devenshire]

Hi,

Lets say you want it to change color as you get within 30, and 60 days:

To conditionally format your cell(s):

In 2003:
1. Select the cells you want to format
2. Choose Format, Conditional Formatting
3. Choose Formula is from the first drop down
4. In the second box enter the formula:
=A1-TODAY()<=30
5. Click the Format button
6. Choose a color on the Patterns tab (or any available option)
7. Click OK one.
8. Click Add
9. For Condition 2, from the first drop down pick Formula is
10. In the second box enter the formula:
=A1-TODAY()<=60
11. Click the Format button
1. Choose a color and click OK twice.

In 2007:
1. Highlight all the cells on the rows you want formatted
2. Choose Home, Conditional Formatting, Manage Rules, New Rule
3. Choose Use a formula to determine which cell to format
4. In the Format values where this formula is true enter the following
formula:
=A1-TODAY()<=30
5. Click the Format button and choose a format.
6. Click OK twice
7. New Rule
8. Choose Use a formula to determine which cell to format
9. In the Format values where this formula is true enter the following
formula:
=A1-TODAY()<=60
10. Click the Format button and choose a format.
11. Click OK tree times.

Now with these formulas anything before today will format as<=30 days. You
can use fancier formulas to deal with this, for example in 2007 you could add
another condition =A1<TODAY() choose no format and make it the first
condition and click Stop If True.

Keep in mind that in 2003 you only have 3 conditions possible, if you want
more you will need to use code.