One way around this is to just change the number format each time a cell
changes.

You could use a worksheet event that looks for changes.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRngToCheck As Range
Dim myCell As Range

Set myRngToCheck = Me.Range("a:a")
If Intersect(myRngToCheck, Target) Is Nothing Then
Exit Sub
End If

Me.Unprotect Password:="hi"
For Each myCell In Intersect(myRngToCheck, Target).Cells
myCell.NumberFormat = "yyyy/mm/dd"
Next myCell
Me.Protect Password:="hi"

End Sub

This checks for changes in column A, then unprotects the sheet, changes the
numberformat, and reprotects the sheet.

To implement this, just rightclick on the worksheet tab that should have this
behavior. Select view code and paste this into the codewindow.

Change the range to match your worksheet, change the numberformat and change the
password (twice).

Esmerelda wrote:

I have just figured out how to protect the format of cells so that when a
date is input it always goes to the same format and font etc, and the format
cannot be changed by anyone. However, when a date cell is copied from another
excel file to my protected cell it does not revert to my desired format. How
can I ensure that whenever anything is pasted into the cell that the format
will automatically revert to my desired format? (ie without having to paste
special) - (maybe some VB code will do this???)

Thank You!

--

Dave Peterson