Would it be possible for you to attach the workbook to an email and send it
to me so that I may examine it closer? Remove spaces from this address:
HelpFrom @ JLathamsite.com
will get it to me.



"Eqa" wrote:

JL,

The macro was written for me so not to up to speed on everything you are
telling me. However Date refers today and from there depending on the age
going back or forward the cell turns a particular colour. Hope this makes it
easier for you to solve my problem.

Thanks,
Eqa

"JLatham" wrote:

Need to investigate a couple of things - what the address of UsedRange is -
perhaps it's not as expected.

Is 'Date' the current date or did you happen to name a cell or range 'Date'
on the worksheet?

Also, once you've hit the Stop statement, or if you simply set a Breakpoint
at the first If statement, you can use F8 to single step through the entire
process, examining values along the way using the immediate window to see
addresses involved and values at those addresses.

For example, typing
? rCell.Address
would provide you with the address of rCell being examined within the For
loop, and
just
? rCell
or
? rCell.Value
would provide you with the value in that range. '?' is short-hand for Print.

"Eqa" wrote:

JL,

That is what I did and renamed it Jul07-Jun08 but the macro only works if I
click on the cell and hit enter or alter it's value. It is as if thye macro
is there on the sheet but doesn't recognise the new name of the sheet.

Thanks,
Eqa

"JLatham" wrote:

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