Hello -
Do you need macro code for that or could you use conditional
formatting?
For conditional formatting do the following:
- Select the cell
- Format Conditional Formatting...
- Condition 1 "Cell Value is" "less than" "=TODAY()-14
- Format... Button Pattern Tab Select Red
- Add Button ... for Condition 2
- Condition 2 "Cell Value is" "less than" "=TODAY()-7
- Format... Button Pattern Tab Select Yellow
- Add Button ... for Condition 3
- Condition 3 "Cell Value is" "less than" "=TODAY()
- Format... Button Pattern Tab Select Green
You can use the following
VB code to add the conditional formatting to
the currently selected cell:
Range("K16").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess,
_
Formula1:="=TODAY()-14"
Selection.FormatConditions(1).Interior.ColorIndex = 3
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess,
_
Formula1:="=TODAY()-7"
Selection.FormatConditions(2).Interior.ColorIndex = 6
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess,
_
Formula1:="=TODAY()"
Selection.FormatConditions(3).Interior.ColorIndex = 10
Selection.NumberFormat = "m/d/yyyy"
I think that would be the easiest way to do it if you want the color to
be updated upon entry. The other option would be to put code in the
Worksheet_Change() callback but that can get tricky too.
Joe