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

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

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

If you want a macro to automatically run when you open the workbook, you need
to have it in the ThisWorkbook code with the name Workbook_Open. The macro
you posted is a worksheet change event and will work when you enter a value
into a target cell that meets the conditions you've set. If you want to run
it as WorkSheet_Open code, you'll also need to identify the worksheet that
the code is run in. At this time, it appears to only work for the worksheet
that it's attached to.

HTH,
Barb Reinhardt

"Eqa" wrote:

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

Hi Barb,

Can you help me with the macro so that it will work with my new shhet as per
my oiginal question.

"Barb Reinhardt" wrote:

If you want a macro to automatically run when you open the workbook, you need
to have it in the ThisWorkbook code with the name Workbook_Open. The macro
you posted is a worksheet change event and will work when you enter a value
into a target cell that meets the conditions you've set. If you want to run
it as WorkSheet_Open code, you'll also need to identify the worksheet that
the code is run in. At this time, it appears to only work for the worksheet
that it's attached to.

HTH,
Barb Reinhardt

"Eqa" wrote:

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

Sample (only) below - Change names according to your current settings///

In your Thisworkbook module copy in:

Private Sub Workbook_Open()
Worksheets("sheet1").Activate
Range("G1").Value = Now()
End Sub

In your sheet1 module post in:

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "The Change Event just Fired"
End Sub




"Eqa" wrote in message
...
Hi Barb,

Can you help me with the macro so that it will work with my new shhet as
per
my oiginal question.

"Barb Reinhardt" wrote:

If you want a macro to automatically run when you open the workbook, you
need
to have it in the ThisWorkbook code with the name Workbook_Open. The
macro
you posted is a worksheet change event and will work when you enter a
value
into a target cell that meets the conditions you've set. If you want to
run
it as WorkSheet_Open code, you'll also need to identify the worksheet
that
the code is run in. At this time, it appears to only work for the
worksheet
that it's attached to.

HTH,
Barb Reinhardt

"Eqa" wrote:

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

Eqa,
If you want the new sheet to behave exactly like the other sheet that has
the code associate with it, instead of inserting a new sheet, simply make a
copy of the one that works properly and rename it. In making the new copy,
the code will be copied also and it will apply to just that new sheet - the
one you made a copy of will still work exactly as it did before.

Hope this helps.

"Eqa" wrote:

Hi Barb,

Can you help me with the macro so that it will work with my new shhet as per
my oiginal question.

"Barb Reinhardt" wrote:

If you want a macro to automatically run when you open the workbook, you need
to have it in the ThisWorkbook code with the name Workbook_Open. The macro
you posted is a worksheet change event and will work when you enter a value
into a target cell that meets the conditions you've set. If you want to run
it as WorkSheet_Open code, you'll also need to identify the worksheet that
the code is run in. At this time, it appears to only work for the worksheet
that it's attached to.

HTH,
Barb Reinhardt

"Eqa" wrote:

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