Traffic Light Date System

paulsolar

Well-known Member
Joined
Aug 21, 2013
Messages
696
Office Version
  1. 365
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


VBA Code:
'>>>> 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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Try this:
VBA Code:
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
 
Upvote 0
Solution
Hi Joe

worked perfectly thanks, I'm going to put your code in my special macro file for the future (y)
 
Upvote 0
You are welcome.
Glad I was able to help!
 
Upvote 0

Forum statistics

Threads
1,224,568
Messages
6,179,591
Members
452,927
Latest member
whitfieldcraig

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top