How to lock a cell only not allowing user change format?

Dear Sirs,

How to lock a cell only not allowing user change format? Allowing user paste values only?

You know when you copy & paste from other excel form that the font and background color, etc are customized, those formats will be carried over to destination cell as well. How can I lock up the destination cell then user can only paste values?

Thanks

Not so easy. Here's an article on it.

http://www.dailydoseofexcel.com/arch...te-operations/

I want to use below code, but what is the definition of "ValidationRange"? is it done by going to Insert -> Name -> Define? There is a dead-loop if to use it.


Private Sub Worksheet_Change(ByVal Target As Range)
'Does the validation range still have validation?
If HasValidation(Range("ValidationRange")) Then
Exit Sub
Else
Application.Undo
MsgBox "Your last operation was canceled." & _
"It would have deleted data validation rules.", vbCritical
End If
End Sub

Private Function HasValidation(r) As Boolean
' Returns True if every cell in Range r uses Data Validation
On Error Resume Next
x = r.Validation.Type
If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function

---------- Post added at 02:57 PM ---------- Previous post was at 01:35 PM ----------

I have read below thread but the suggestion there doesn't work.

http://www.excelforum.com/excel-gene...08#post2871608

Ohlalayeah,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here