Thanks Otto.

I've edited the code as shown below and put it in the Sheet1 Object page but
when I change the cells in B3 nothing happens in C3.

Can't see what I've done wrong but I must have missed something.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "B3" Then
If Target = "£" Then
Target.Offset(, 1).NumberFormat = "£#,##0.00"
Else
Target.Offset(, 1).NumberFormat = "General"
End If
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
End Sub