Hi Gordon,

here is a copy of the code I would like it to automatically work when I open
the workbook rather than having to manually make it run. Hope this makes
sense and you can help me. Thanks.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim rCell As Range

Set rng = Intersect(Columns("D"), Me.UsedRange)

If Not Intersect(rng, Target) Is Nothing Then
For Each rCell In Target.Cells
With rCell
If IsDate(.Value) Then
.FormatConditions.Delete
Select Case .Value
Case Is Date + 6: .Interior.ColorIndex = xlNone
Case Is = Date: .Interior.ColorIndex = 3
Case Is Date - 30: .Interior.ColorIndex = 7
Case Is Date - 60: .Interior.ColorIndex = 6
Case Is Date - 90: .Interior.ColorIndex = 46
Case Else: .Interior.ColorIndex = 15
End Select
Else
.Interior.ColorIndex = xlNone
End If
End With
Next rCell
End If
End Sub

"Gord Dibben" wrote:

You say a "macro attached to a sheet".

How is the macro run? Triggered by an event or button or?

Do you mean worksheet event code which is triggered by an event in that
worksheet.

Sheet event code is for that sheet only.

Maybe if you pposted your code someone could help.


Gord Dibben MS Excel MVP

On Fri, 6 Jul 2007 21:08:03 -0700, Eqa wrote:

I have a macro attached to a sheet in a workbook called "sheet1" I have
inserted a new sheet in front of sheet 1 called "Jul07-Jun08" but the macro
no longer works with the new sheet. What do I need to insert into the macro
and where in the macro to enable the new sheet t respond to the macro?

Thanks

Eqa