MrExcel Publishing
Your One Stop for Excel Tips & Solutions

colour change after time..

Posted by Andrew Richards on February 11, 2002 4:39 AM

With a Macro is it possible for a cell to change colour after a certain time/ date period has elapsed. I.e after 1200 today can a cell turn blue?

Thnks for any ideas.

Posted by DK on February 11, 2002 5:57 AM


There are several ways you could achieve this using VBA. Here's one:-

Right click the worksheet tab and choose View Code. Then pick Worksheet from the first combobox (there are two boxes near the top of the VB editor) and choose Calculate from the right. This will give you a blank procedure which is fired every time the worksheet recalculates. To do something like you're after try this:-

Private Sub Worksheet_Calculate()
If Time > #12:00:00 PM# Then
Range("A1").Interior.ColorIndex = 5
End If
End Sub


Posted by Andrew Richards on February 11, 2002 6:20 AM


Great thanks for that.
If though I have several cells with different times in them and I want all of them to change colour, e.g 2 hrs before each one, is there a way this can be done?