When I copy-paste text from one cell to another, conditional formatting is pasted also. I have tried to paste only text using Paste Special, but then my merged cells are getting unmerged. How can I copy-paste text without affecting conditional formatting and my merged cells? If this is possible to do, how can I make it default so that every time I paste something it does it that way?
Edit - Paste Special - Paste Special: set the desired options.
Don’t use merged cells, you see it just causes trouble.
@AkeMake, I’m afraid you’ll have to copy the range cells first and then remove the conditional formatting.
@AkeMake : There are different descriptions in your Title:
Copy Paste without conditional formatting and without affecting merged cells
… and your Text :
so what?
I don’t understand what is the difference in my title and the text.
I am sorry, but so far solutions haven’t helped at all. As I said, I have tried “Paste Special” option, but it doesn’t work the way I want. Or at least I couldn’t find options which do it. I need those cells to be merged. Cannot do without it.
I try to explain the problem in more detail.
I have made an automated calendar for my customer. In this calendar, each date is made of two cells merged into one. Each merged cell (date) has its own conditional formatting to change its background colour when needed. My customer will write some text into this calendar and many of the calendar dates will have the same text with each other. That’s why I would like to find a way how my customer can copy-paste these texts between different dates without affecting conditional formatting or breaking merged cells. Now normal copy-paste will take conditional formatting from the copied cell (date) and replace conditional formatting in the pasted cell. If I choose “Paste special” and “Paste only text”, then conditional formatting is correctly untouched but the calendar date where I paste will get unmerged back to two cells.
Of course, I could tell my customer not to use copy-paste but to write everything by hand. However, this would give a lot of extra work which could be avoided if this kind of copy-paste was possible.
I tried the “Protect Sheet” option.
First I chose calendar dates and went to Format → Cells… and from the Cell Protection tab I unchecked “protected” and checked “hide formula”. Then I chose Tools → Protect Sheet. It sounded like this would be the solution to the problem but unfortunately, it wasn’t. Conditional formatting is still copy-pasted along with the text.
Does anyone have any ideas about what I should do?
Let’s try to approach the problem from the other side. Perhaps it is possible to organize the data so that the conditional formatting is copied but not displayed? What I mean is that the condition for formatting can be constructed in such a way that the formatting will fire in the source cell, but will not fire in the target cell. What condition are you using in your conditional formatting?
There are a few conditional formatting but one, for example, is changing the background colour of the date if it is a public holiday. The calendar is automatic the way that the customer can write down the year he wants and the calendar will automatically show dates correctly for that year. For example, the 1st Jan is Monday in 2024 but Sunday in 2023. Hence, public holidays are falling into different places in different years.
Now if I copy-paste the cell from 6th Jan to 8th Jan and the 6th happens to be a holiday, then the cell for the 8th day will change colour because the 6th is a holiday.
This is one conditional formatting among a couple of others.
It sounds like the condition is using an absolute cell address rather than a relative one. But a verbal description is not enough. Can you show a screenshot of the condition of format? So that the applied formula is visible