See if this works.

Right click the sheet tab > select view code > paste the below in.

Type 23/10/07 into any cell in Col A and Col B will return that date

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If IsDate(Target) = True And Target = "23/10/2007" Then
Target.Offset(0, 1).Value = Target.Value
End If
End If
End Sub
VBA Noob