Copy and Paste based on cell color index

[js999]

Hello,

Here is what I need help with. I am copying and pasting data from one worksheet into 3 other worksheets. For the cells shaded with the color 'Rose' I only want to paste values. For the cells that are shaded in 'Pale Blue' I want to paste the formulas.

The range I want to copy from is always E96:T96. While the 3 worksheets are named differently, the region to paste into them always is cell A1.

How could I use the colorindex property of the selection to do this? Is it even possible?

If my reply to your response is slow, it isn't because I'm disinterested, it is because I am in a meeting.

Thanks,
-js999

[shg]

Something like this:

Please Login or Register  to view this content.

[js999]

shg,

Hmmm. Interesting.

Thanks for the reply. Let me see if I can get it to work.

-js999

[scm2000]

I realize this is seven years later, but I found this on a search and it looks like it does exactly what I want. However, it doesn't run for me. I modified it for my worksheet names and data range, but I get a "Compile Error:Argument not optional" when I run Sub test(). Sub BlueNRose is not even recognized as a macro.

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

Please Login or Register  to view this content.

I played around with it for a couple of hours and cannot get it to work. I can't set or dim rFr and rTo. I'm just a beginner, but really need this to work as I have a large amount of non-contiguous data to copy and the macro gets too large when I copy/paste each data block separately.

Thanks in advance!

[6StringJazzer]

scm2000, your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

I see that you have already started a new thread:

http://www.excelforum.com/excel-prog...el-2007-a.html