Thanks, I found one more solution:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns(9)) Is Nothing Then
On Error GoTo bm_Safe_Exit
Application.EnableEvents = False
Dim rng As Range
For Each rng In Intersect(Target, Columns(9))
'make safe anything that already has been changed
rng = Replace(rng.Value2, " / ", ChrW(8203))
'change new spaces
rng = Chr(39) & Replace(rng.Value2, Chr(32), " / ")
'try to repait Excel's auto-date-conversion
If IsDate(rng) Then Application.Undo
'restore the safeties
rng = Replace(rng.Value2, ChrW(8203), " / ")
Next rng
End If
bm_Safe_Exit:
Application.EnableEvents = True
End Sub
Bookmarks