Looking for solution to problem if anyone can help please ...

Column A contains a list of date values (e.g. 11/04/2005).

I'm looking for a way to change the cell color based on the following
criteria ...

If the date falls earlier than today but no earlier than a week before
today then cell color to be red.

If the date falls earlier than today but no earlier than two weeks before
today then cell color to be yellow.

If the date falls earlier than today but no earlier than three weeks before
today then cell color to be green.

I have used the sample code as detailed here ... http://tinyurl.com/3cex5
(kindly advised by Max in microsoft.public.excel) and need to tailor the
code to suit the different date ranges. Can anyone help out please as I'm
not sure how to build this is to the CASE statements.

Many thanks.

--
T. Denford.

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

On 13 Apr 2005 03:56:10 -0700, Joe HM wrote:

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

Hi Joe,

Yes I need VB code to do this as I need to build in more than 3 conditional
formats.

--
T. Denford.

Hello T -

Here we go ... the following code applies the colors to the A1 Cell but
you can tweak it to work for a range or whatever you need ...

Private Sub Worksheet_Change(ByVal aTarget As Range)
If aTarget.Column = 1 And aTarget.Row = 1 Then
Set lTargetCell =
ThisWorkbook.Sheets("Sheet1").Cells(aTarget.Row, aTarget.Column)

If lTargetCell.Value < Date - 14 Then
lTargetCell.Interior.Color = vbRed
ElseIf lTargetCell.Value < Date - 7 Then
lTargetCell.Interior.Color = vbYellow
ElseIf lTargetCell.Value < Date Then
lTargetCell.Interior.Color = vbGreen
End If
End If
End Sub

Joe