Traffic Light Date System

[paulsolar]

Hi All

I'm looking at doing a very simple traffic light system for colour coding dates at the end of a very long macro

Something like the below, I know this doesn't work but i have never attempted this type of thing before.

Any help would be appreciated. the dates are formatted dd/mm/yyyy

cheers

Paul

'>>>> colour dates
Dim MyDate
MyDate = Date

With Worksheets("Import").Range("C2:C" & LastRow)
If Value < MyDate Then
Interior.Color = RGB(255, 0, 0)
Font.Color = RGB(255, 255, 255)
Else
If Value = MyDate Then
Interior.Color = RGB(255, 255, 0)
Else
If Value > MyDate Then
Interior.Color = RGB(0, 255, 0)
End If
End With

 

[Joe4]

Try this:

Dim LastRow As Long
Dim cell As Range

With Worksheets("Import")
'   Find last row in range
    LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
'   Loop through cells
    For Each cell In .Range("C2:C" & LastRow)
        Select Case cell.Value
            Case Is < Date
                cell.Interior.Color = RGB(255, 0, 0)
                cell.Font.Color = RGB(255, 255, 255)
            Case Is = Date
                cell.Interior.Color = RGB(255, 255, 0)
            Case Is > Date
                cell.Interior.Color = RGB(0, 255, 0)
        End Select
    Next cell
End With

 

[paulsolar]

Hi Joe

worked perfectly thanks, I'm going to put your code in my special macro file for the future

 

[Joe4]

You are welcome.
Glad I was able to help!