Hi Rick

In case you are wanting to get on with this this morning, and Dave won't
(probably) be on line yet, you need to make changes
to
Const WS_RANGE As String = "j2" change to "j1"
as J1 is now the cell where you are making the change to the date.
and
Me.Name = format(Target.Value, "dd mm") to
Me.Name = format(Target.Value + 7, "dd mm")


--
Regards

Roger Govier


"Big Rick" wrote in message
...
Thanks Dave,
This does work better but I would like to take it one stage further.
What I would like is to have a formula in J2 (e.g. Info!A1+7) with
Info!A1 =
1/1/06.
Now if Info!A1 were to change, could I have the tab name change
automatically.
By the way, I really am clueless when it come to macros. Remember my
post on
date formatting last week which started a great debate !) So please
can you
answer in laymans terms.

Thanking you in anticipation.
--
Big Rick


"Dave Peterson" wrote:

Worksheet_change waits for you to type something. (Your code checks
to see if
you typed something in J3--if not, it doesn't do anything.)

Since J3 is a formula, it's not changed by typing.

You could use the worksheet_calculate event, but I think I'd just
start looking
at J2:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "j2"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Name = format(Target.Value, "dd mm")
'me.name = target.text
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

You could use Target.Text if you formatted that cell the way you
wanted.