What do I need to add to the macro listed below to make the cell colors
change to yellow after 1 day change and to red after 2 days?

"Bojames" wrote:

What do I need to add to the macro listed below to make the cell colors
change to yellow after 1 day change and to red after 2 days? Below is the Macro.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("C2:C1000"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

This event procedure runs when someone changes a value in C2:C1000.

If the user doesn't change a value in that range, then this macro won't do
anything.

Maybe you could add format|conditional formatting to that range that compares
the dates in column D with =today() (or =now()).

I would do it manually as a one time setup.

But if you wanted, you could record a macro and actually add that conditional
formatting to the cells in column C.

Bojames wrote:

"Bojames" wrote:

What do I need to add to the macro listed below to make the cell colors
change to yellow after 1 day change and to red after 2 days? Below is the Macro.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("C2:C1000"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

--

Dave Peterson

Thanks for the advice. After I posted this I changed my mind. I need it to do
three colors at intervals of 8 hours.

"Dave Peterson" wrote:

This event procedure runs when someone changes a value in C2:C1000.

If the user doesn't change a value in that range, then this macro won't do
anything.

Maybe you could add format|conditional formatting to that range that compares
the dates in column D with =today() (or =now()).

I would do it manually as a one time setup.

But if you wanted, you could record a macro and actually add that conditional
formatting to the cells in column C.

Bojames wrote:

"Bojames" wrote:

What do I need to add to the macro listed below to make the cell colors
change to yellow after 1 day change and to red after 2 days? Below is the Macro.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("C2:C1000"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

--

Dave Peterson

And xl2003 and below provide up to 3 different conditional formats for you.

Bojames wrote:

Thanks for the advice. After I posted this I changed my mind. I need it to do
three colors at intervals of 8 hours.

"Dave Peterson" wrote:

This event procedure runs when someone changes a value in C2:C1000.

If the user doesn't change a value in that range, then this macro won't do
anything.

Maybe you could add format|conditional formatting to that range that compares
the dates in column D with =today() (or =now()).

I would do it manually as a one time setup.

But if you wanted, you could record a macro and actually add that conditional
formatting to the cells in column C.

Bojames wrote:

"Bojames" wrote:

What do I need to add to the macro listed below to make the cell colors
change to yellow after 1 day change and to red after 2 days? Below is the Macro.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("C2:C1000"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

--

Dave Peterson


--

Dave Peterson