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

3
votes

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?

1
excelvba
Why would you want two tables where the only difference is the color anyway? You could also simply put in F8: =B8 – Luuklag
Hello! Did my answer help you? – ttaaoossuuuu

1 Answers

0
votes

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