Change Font Colour According to Date

Microsoft Office excel 2007 step by step
June 21, 2010 at 02:03:00
Specs: Windows XP
Can you help please? I am trying to format cells in an excel 2007 spreadsheet, so that one month before the date shown in each cell is reached, the font changes colour to red.

Many thanks,

Paul


See More: Change Font Colour According to Date

Report •


#1
June 21, 2010 at 04:23:56
You can use conditional formatting.
If B1 contains your date, do this:

From the Ribbon select Home - Styles - Conditional Formatting,
From the drop down select 'Manage Rules' and select 'New Rule'
In the dialog box select the last item in the list 'Use a formula to determine which cells to format'
Enter this formula in the box

=IF(B1<TODAY()+28,TRUE,FALSE)

Note that TRUE and FALSE do not have double quotes - they are Excel's logical values true and false.
Click the format button and from the 'Font' Tab select a red color, click OK

You can make this more sophisticated by adding additional conditions!
Click OK and select 'New Rule'
Select 'Use a formula to determine which cells to format'
Enter this formula in the box

=IF(B1<TODAY()+35,TRUE,FALSE)

Click the format button and from the 'Font' Tab select a yellow color, click OK
'Applies to' will show the selected range, and the 'Stop if True' boxes are checked.
Click Apply and OK

Now dates between 28 and 35 days have yellow font and red font if less than 28 days.

Note that conditional formatting can be copied to other cells using Copy and Paste Special ... Formats, or select a range of cells to create the conditional formatting in and write the formula to apply to the first cell in the range. The formula will automatically be corrected for each cell in the range.

Regards


Report •

#2
July 4, 2010 at 01:17:24
I have followed your instructions and now have a database that does exactly what I want it to do - fantastic! Thank you very much.

Report •

#3
July 4, 2010 at 05:15:48
You're welcome

and thanks for the feedback

Regards

Humar


Report •

Related Solutions


Ask Question