I thought that the code I provided did that.

I just tried it again and it worked (again) for me.

try adding a message box in the code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myFormulas As Variant

MsgBox "I made it here!"

On Error GoTo errHandler:

myFormulas = Target.Formula
With Application
.EnableEvents = False
.Undo
End With
Target.Formula = myFormulas

errHandler:
Application.EnableEvents = True

End Sub

When you make a change, do you get the message box?

If yes, then I don't have another suggestion.

If no, then you may have put the code in the wrong location (it belongs under
the worksheet).

Or you may have macros disabled or you may have disabled event processing.

I'm still guessing the wrong location.

The code remembers the formulas (myformulas = target.formula). Then it does an
undo so it's back to its previous state. Then it assigns the formulas--just the
formulas--no formatting.



Rick wrote:

Dave Peterson wrote:
I just hope it works as well as George's solutions!

Debra Dalgleish wrote:

That question Simms familiar.

Dave Peterson wrote:

What happened when you tried it?


I guess something happened to my last reply...hmmm?

Well Dave, the code did nothing. The values and the formatting changed when I used a copy and paste.
Is there a way to trap a paste event and turn it into a PasteSpecial "Values Only"?

Rick

--

Dave Peterson