I have the code below working as I want it to, when data is input in column 2 the date and time is automatically input into the corresponding cell 3 columns to the right. I want to ensure that the date is changed to the UK format dd-mmm-yy. I have formatted the column to be in that format but for some reason it has suddenly started changing the date to US format.
Can this code be changed to force the date to be in UK format?
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Column = 3 And .Offset(0, 3).Locked = False Then
ActiveSheet.Unprotect "test"
ActiveSheet.Unprotect
.Offset(0, 3).Value = Date & " " & Time
.Offset(0, 3).Locked = True
ActiveSheet.Protect "test"
End If
End With
End Sub
Regards
Geoff
Bookmarks