Conditional Formatting and Paste Special

[nate777]

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.

Thanks,
Nate

 

[erik.van.geit]

Hi, Nate,

instead of "all", did you try "values" ?
or wouldn't that work for you ?

kind regards,
Erik

 

[nate777]

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?

 

[erik.van.geit]

I just started up an old PC with Excel 2000 to be sure

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

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

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

enter 12 in A10
background A10 changes to yellow

not for you ??

 

[nate777]

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.

 

[erik.van.geit]

I see what you mean.

Again, copy the source sheet (B2)

you don't need to copy again !
just paste twice

 

[nate777]

You have been very prompt in answering my questions. Thanks Eric!

 

[erik.van.geit]

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