I ran into something similiar to this with needing to make sure certain cells
weren't changed. Every time a user selected a cell I copied the values and
formats etc to a hidden cell. and whenever they changed a cell i compared the
hidden cell's values and formats to the one the user changed, if I didn't
like the changes I copied the hidden cell to the user changed one.

"Joe HM" wrote:

Hello -

I was wondering if there is a way in an Excel macro where I can catch
the paste event to make sure the user only copies/pastes the values of
a field and not the format?

I have some conditional formatting and if the user copies a certain
cell, things get screwed up. Is there also a way to block a cut/paste?

I tried relative formatting but for some reason things get screwed up
when I run a macro that creates the relative formatting:

With lTPSheet.Range("B" & lSelection.Row & ":E" & lSelection.Row)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=$D" &
lSelection.Row & "=""X"""
.FormatConditions(1).Font.ColorIndex = 2
.FormatConditions(1).Interior.ColorIndex = 3
End With

The Formula1:="=$D" & lSelection.Row & "=""X""" does NOT set the
correct row for some reason (usually off by 3) if it is a relative
reference rather than absolute ($).

Very strange unless I'm really missing something ...

Thanks!
Joe