Hi Rick

Sorry, I hadn't appreciated it was 2 separate sheets.
The following seems to work OK for me

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1"
Dim ws1 As Worksheet
Set ws1 = ActiveWorkbook.Worksheets("Sheet2")

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With ws1
.Name = Format(Target.Value + 7, "dd mm")
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub



--
Regards

Roger Govier


"Big Rick" wrote in message
...
Thanks for replying Roger.
This is still not quite what I'm after. I will try and explain
differently.
Say Info!A1 is "1/1/06" and on Sheet2, J2 is "=Info!A1+7" which should
make
the tab name "08 01"
If I were to change Info!A1 to 2/2/06 then I require Sheet2 tab name
to
change straightaway to "09 02".
What I am having to do now is to actually click in Sheet2!J2 cell and
press
enter to make the tab name change. Dave suggested something about a
Worksheet
Calculate Event but as you know, I wouldn't know where to start.
Hoping that this explains further.
--
Big Rick


"Roger Govier" wrote:

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.