In cell c4 i have a link to a cell in another sheet
In cell c5 i have the formula ="OF_"&C4.
How do I get excel to automatically rename the sheet to the content i C5.
I have tried right click the sheet and select 'view code' and pasted:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error Resume Next
With Target
If .Address = "$A$1" Then
Me.Name = .Value
End If
End With
Application.EnableEvents = True
End Sub

into it.
It seems that this macro only works when you enter the text manually into
the cell c5 i.e. it is not automatically updated when I change the content of
the cell which c4 refers to.
Any ideas?

Also, will there be a problem when I protect the sheet and the cell C5 has
the format 'locked', and if so can I get around that without having to unlock
the cell.
Thanks
/Adam