Force "Match Destination Formatting" for a particular column/worksheet

[mrlex]

Hi all,

I am trying to create a tool for myself and others to use
As part of this I have set up various conditional formatting rules which will highlight certain bits of information to users etc.

I want the tool to be as easy to use (and as hard to break) as possible but I am having some trouble. People will need to enter info into the tool daily and often people will copy and paste from an external source (eg. emails/online etc).

The issue is that often the paste function will copy external formatting into my tool that messes up the conditional formatting rules I created. This is easy to avoid if you simply right click “Match destination formatting” but I would much rather the tool do this by default.

Obviously I could just train people to do this but inevitably this will be forgotten and it is causing issues which people don’t know how to fix.

Does anyone know of a way to force excel to “Match destination formatting” for a certain column?

Thanks in advance

 

[mrlex]

Should point out that I am open to any work around also (including something VBA based if necessary).

I believe you can set Excel to default to a certain paste style but I believe this is in Excels settings and so would likely not translate if a new machine opened my worksheet.

Thanks again!

 

[mrlex]

I figured this one out on my own in the end:

You need to set up a new macro and assign the keyboard shortcut Ctrl-V
Record yourself special pasting a value using Match Destination Formatting.
Then stop recording.

That way the Ctrl-V paste function is replaced with the Match Destination Formatting paste function.

Might have guessed it would be a simply solution

Hope this helps someone