VBA Macro for only pasting values OR match destination format

I have a spread sheet setup for people in my office to use with some conditional formatting, but when some people copy and paste items into the spreadsheet it's messing with the conditional format cell ranges.

I currently have some code in for a macro when Ctrl+V is pressed to paste special > values which is a great work around so far. But on occasion some people will copy and paste between instances of excel, in which case I'd need it to paste special > match destination formatting. How can I also allow this in the code I already have?

Please Login or Register  to view this content.

Originally Posted by kev_

Let's take a step back

Is your problem that
- they are pasting formulas into the cells
- that they are removing conditional format rule on pasted cells
- or both?

Is it helpful to allow them to copy & paste - or would you prefer to prevent that from happening?

When they are copying and pasting, are they pasting "one cell at a time" or a "range"?

Hi Kev,

I wouldn't want to stop them from copy and pasting as sometimes the text in the cell can be quite long so it would be pain to have to retype it all.

It's essentially a calendar for everyone to fill in their tasks for the week, the weekday highlights depending on todays day with conditional formatting. But as they are copy and pasting between days it's messing with conditional formatting, and so random cells are also highlighting when they shouldn't be.

They can either be copy and pasting one cell at a time or a range of cells depending how many tasks they're coping across.

Hope this helps.