have a range of cells shade a certain color

[akeshka]

I want a cell to be shaded a certain color based on today's date versuses the
date in the field upon opening the worksheet daily. For example if the date
in the field in 02 Apr 2008 and today is 14 Apr 2008 the cell should
automatically change to let's say red when the workbook opens. At the same
time if the date is 14 Apr 2008 and the date in the cell is within 30 days it
should be shaded yellow etc...

Can you help?

 

[Office_Novice]

Try this
Sub WorkSheet_Open()
'Change ActiveCell to what ever you need it to be i.e. ("A1")
If ActiveCell.Value > Date Then
ActiveCell.Interior.ColorIndex = 3
End If
End Sub

 

[Office_Novice]

Or this ...

Private Sub Worksheet_Activate()
Dim Rng As Range
Dim i As Variant

Set Rng = Range("A1:A36") ' <------Change to Suit your range
i = ActiveCell.Value

For Each i In Rng
ActiveCell.Offset(1, 0).Select
If i = Date Then
ActiveCell.Interior.ColorIndex = 3
ElseIf i < Date Then
ActiveCell.Interior.ColorIndex = 6
End If
Next i
End Sub