Conditional Formatting and Paste Special


New Member
Aug 2, 2006
Hi Again,

I'm trying to copy-paste select columns of data from sheet1 to sheet2(eg: copying columns A,B,C F,I J from Columns A thru O). and this is the method i follow,

Copy the selected columns from Sheet1
I select a cell in sheet 2 (where I want data pasted) right click and select the paste special option.
In paste special, I ensure the All option is chosen and then click on paste link.

My data is pasted however, I seem to be losing the conditional formatting coloring that I applied in sheet1.

Can anyone out there, please let me know the correct approach.


Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Thanks Eric, I tried using "values" it does not help. In paste special (excel 2000) , It does not let you select multiple options (e.g formulas,validation,formats...), Which I need, since my sheet1 has calulated values,manual values(in some),conditional format and validation applied.

When I just use the paste option, the conditional formatting works as long as sheet1 cell has a manual entry instead of a calculated value/formula.

I want to have calculated values in sheet1 and have conditional format retained in my pasted areas. Is this possible?
Upvote 0
I just started up an old PC with Excel 2000 to be sure

you can choose paste"values" without disturbing the conditional formats

new sheet
select A1:A10
conditional format: cellvalue equal to 12
color background yellow

A5:A10: fill in 12 (background changes to yellow)
copy cell B10
rightclick in A10
choose pastevalues
in the dialogbox choose "values"
(background A10 changes to nothing)

enter 12 in A10
background A10 changes to yellow

not for you ??
Upvote 0
It works Eric!

I don't think I stated my problem correctly, and it looks like I have found a solution, not neccessarily the best.

Background info:

I have a source sheet(sheet 1) and Report sheet. Source sheet is where data is entered and Report sheet has selections of data from source sheet. The report sheet is sent out as a report to users.

In the source sheet, I have column headings A1= Reported Date B1= Due Date.The due date gets caculated as 30 days from reported date, So I have in B2 a formula=A2+30.

Now, I also have conditional format applied on B2.(I used formulas in conditional format). B1 turns red,yellow or green depending on if due date is past due, current or a date in future.

Now, I want to copy cell B2 and paste it in the report sheet.. Again, I want to ensure the pasted cell carries the conditional format and also updates automatically if source sheet cell value changes.

Here is the solution

Copy Source sheet (eg:B2).

Select destination cell in Report Sheet, right click and select paste special

Select "All" and "Paste Link"(At this point, you can see your cell is pasted,but neccessarity the conditional formatting color)

Again, copy the source sheet (B2) and in the destination cell,right click and select paste special.

Now select "format" and click OK.

This ensure that the pasted cell has carried everything from the orignal cell. I'm recording macro for the above action. so, I believe will solve my problem.

Any other solutions, please let me know.
Upvote 0
you're very welcome :)
this was my free day for MrExcel

answering, yes, and learning a lot
the list of my todays learned-items is long
Upvote 0

Forum statistics

Latest member
Mayank Sharma

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back