CODEWITHSUNDEEP
excelvba
guffka
guffka

Reputation: 33

Restrict copy/paste to be Values only for not changing conditional formatting

For cells that have conditional formatting, for example, Fill black color if cell contains blank, if the cells are copied and pasted to other place, the conditional formatting will change too.

Example Picture:

enter image description here

I understand that using "Paste Values" can keep the conditional formatting, but can we restrict the copy/paste mode to be "Values" only, so that user can only use CTRL+C/CTRL+V?

Upvotes: 3

Views: 3052

Answers (1)

ttaaoossuu
ttaaoossuu

Reputation: 7894

I will start with a question. Would you buy a book without page numbers? Would you drive a car with steering wheel operating in the opposite direction? Would you cross a street where green lights are replaced with purple and red ones are replaced with blue? Would you use Excel where Ctrl+V does something funny instead of just paste? Replacing standard functionality that has been there for decades is highly NOT RECOMMENDED. User may view this as unexpected behavior.

But still, if you need it, this is possible, via VBA macro like this (bind this to Ctrl+V in Macros menu):

Public Sub PasteValues()
    If Application.CutCopyMode Then
        Selection.PasteSpecial Paste:=xlPasteValues
    Else
        ActiveSheet.Paste
    End If
End Sub

Upvotes: 0

Related Questions