Color Code Dates in Excel

[Mspearman]

I have two worksheets, second worksheet is linked to the first and provides color codes for dates for the second sheet. The for dates that are within 90 days form the date listed on sheet one the date turns green on sheet two, then amber for 60, red for 30, and gray for overdue. Is there a way to do this same function without having to link sheet two to sheet one? Is it possible to do this in VBA or use some other method? If so how?

Here are the formulas I used on sheet two to link to sheet one.

=EDATE(NUMBERVALUE('FORMULA SHEET'!B2),13)


=NUMBERVALUE('FORMULA SHEET'!C2)


=EDATE(NUMBERVALUE('FORMULA SHEET'!D2),NUMBERVALUE('FORMULA SHEET'!E2))


=EDATE(NUMBERVALUE('FORMULA SHEET'!F2),24)

 

[Zot]

Here is the VBA to color the date.

Sub ColorDate()

Dim NumberOfDay As Long
Dim DateA As Date, DateB As Date
Dim wb As Workbook
Dim wsA As Worksheet, wsB As Worksheet

Set wb = ThisWorkbook
Set wsA = wb.Sheets("A")      ' Define your sheet name here
Set wsB = wb.Sheets("B")

NumofDay = DateDiff("d", wsA.Range("A1"), wsB.Range("A1"))

Select Case NumberOfDay
    Case Is < 91
        wsB.Range("A1").Interior.ColorIndex = 4                   'Green
    Case Is < 121
        wsB.Range("A1").Interior.ColorIndex = 44                 ' Orange
    Case Is < 151
        wsB.Range("A1").Interior.ColorIndex = 3                   ' Red
    Case Else
        wsB.Range("A1").Interior.ColorIndex = 16                 ' Gray
End Select

End Sub

You can refer to color from any website such as

VBA: ColorIndex Codes List & RGB Colors

In this ArticleVBA Color Index Codes ListVBA ColorIndex ExamplesVBA Color PropertyVB ColorRGB ColorsColorIndex Codes List & RGB Colors in Access VBA VBA Color Index Codes List When using VBA to code the Colorindex (or background color) of a cell it is useful to know what integer will equal what...

www.automateexcel.com

 

[Mspearman]

Zot,

Thank you for your help with this I will give it a try and let you know how it turns out!