Cell shading depending on which day

[Guest]

Once more with feeling...
I would like to automatically shade a renge of cells depending what day of
the (working) week it happens to be. So, for instance, cells with data
entered today would be shaded green as would any data entered next Monday in
the same spreadsheet. Tuesdays could be yellow and so on.
The spreadsheet itself doesn't have the days entered anywhere, but it does
have the date.
Can this be done?

 

[Guest]

Try this: you will to adjust colours as required
'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "A1:A100" '<==== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case Application.Weekday(.Value, 2)
Case 1: .Interior.ColorIndex = 10 'green (MONDAY)
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 3 'red
Case 5: .Interior.ColorIndex = ??? 'whatever
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.