i have a formula problem with excel that i need help with

okay i have a list of people that have 4 course dates next to there names in seperate coloums. as those dates get closer to the current date i need there name and date to move to the top of the list so i can get refresher courses done. currently i am going through and moving them to the top in singles the ones that are over due turn red, when they are 2 weeks out from been over due they turn yellow and at 4 weeks out they turn dark green and anything else they are just white if you could give me a formula or a place where i can find one that would be great

or even if it hits a certian colour it moves them to the top of the list in the colour order of red yellow green white

oh and this is in excel 2003

thanks kris

i need 4 different coloums set up so that when the date in respective cells get to 2 weeks and 4 weeks from the current date they change colour 2 weeks out needs to highlight the cell yellow and 4 weeks out from current date it highlights cell dark green and when they are older than the current date they go red

any help is welcome

thanks kris

• Merged by Jaynet ZhangMicrosoft contingent staff, Moderator Monday, February 18, 2013 7:37 AM duplicate case

Hi Kris,

I made an small example for you. I write some vba code to do that, just check to see whether this is what you want:

And the macro code is here:

Sub color_date()
Dim UserRange As Range
Dim day_number As Integer
Dim today_date As Date

On Error Resume Next
Set UserRange = Application.InputBox( _
Prompt:="Select the range of dates", _
Title:=Title, _
Default:=ActiveCell.Address, _
Type:=8) 'Range selection
On Error GoTo 0

today_date = Date

For Each cell In UserRange
    day_number = DateDiff("d", cell.Value, today_date)
    Select Case day_number
    Case Is > 0
        Range(cell.Address).Select
        With Selection.Interior
            .Color = 255
        End With
    Case between - 14 To 0
        Range(cell.Address).Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = -0.499984740745262
            .PatternTintAndShade = 0
        End With
    Case between - 28 To -14
        Range(cell.Address).Select
        With Selection.Interior
            .Color = 65535
        End With
    End Select
Next

End Sub

After run the macro, it will ask you to select the range of dates, just select the cells which contains the dates then click OK.

so will this work running off todays date and the date as the year goes on or do i have to enter the current date everytime i open this spread sheet

thanks for your reply to

No, you do not need to. It will get today's date in the code.

Dear Jaynet Zhang, 

I hope if someone here can help me with a formula where I want to get how many days it is due. I want to subtract due dates with the current day and keeps on updating or incrementing with 1 on the next day. And, using Excel 2013 

Thanks if you can help me with this in advance :) 

Regards,
Flava