Conditional Formatting Help-cell to change to re

**tim1218** · 06-22-2009, 01:48 PM

Hey all,

I'm working on a spreadsheet in Excel 2007 and I need some help with Conditional Formatting. Here is an example of what I am doing

Name Haz JI
Smith, A 08/09/2008

The Haz and JI are the classes the people can/have taken. The date underneath is when they were taken. What I want to do is, the Haz course has a refresher class that needs taken after every year. Whenever the year has past, I want the cell to change to red, letting me know it is overdue. Also, I would like to change the cell to yellow whenever it is within 3 months of that year.

If you want me to explain it better, I will try. Any help would be greatly appreciated.

Thanks,

Tim

**NBVC** · 06-22-2009, 02:02 PM

Select range to format

Go to Home tab and select Conditional Formatting >> Manage Rules

Click New Rule,

Select Use a formula to determine which cell to format...

enter formula:

=TODAY()>=DATE(YEAR($C3)+1,MONTH($C3),DAY($C3))

where C3 is top left most Cell to colour in your selection.

and click Format... select Red Fill

Click Ok..

Click New Rule

Repeat as above with formula:

=TODAY()>=DATE(YEAR($C3)+1,MONTH($C3)-3,DAY($C3))

Click Format and choose Yellow fill

**tim1218** · 06-22-2009, 02:19 PM

Wow, that was a fast response.

Thanks a lot NBVC.

EDIT- It works.

**NBVC** · 06-22-2009, 02:41 PM

You may need to switch the conditions, by selecting one and click the up/down arrow beside the Delete button in the conditional formatting rules manager dialogue.

**NBVC** · 06-22-2009, 03:01 PM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save