Hi Ricky,

The macro already places only the values in the cells (using the line of
code you reference below). The reason the formatting is being removed is
that I was under the mistaken assumption that the format of the destination
area should be reset prior to adding the new shading. If you delete the
following line of code from further down:

rngDest.Interior.ColorIndex = xlNone

The original format of the destination cells will not be affected.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Ricky Pang" wrote in message
...
Hi Rob,
Please let me extend my gratitude to you for helping me even while
you're on vacation. You've been graciously considerate. Your code
works like a charm! Just amazing!! With your explanation of the run
time error, I figured that it was the title called Date in cell A1. So
the entire column A wasn't really all dates because of that text title
cell. Thanks to your adjustment, it works now and it's a thing of
beauty.

I hope the Mrs. won't be mad me for asking this.(j/k) She is a very
important person indeed.
Towards the end of the macro, how do I change the following line so that
the paste feature, pastes only as values? This is to maintain the color
fill background that was originally there (before the macro was run).
An excerpt of that code:

''' Add the name to the destination range
rngDest.Value = szName

Perhaps other experts might lend a hand while Rob enjoys his time away.
I will continue to work on other areas of my spreadsheet towards
completion. I may have more questions in the future. In the meantime,
I'd like to say thanks again Rob.
Have a great holiday!

Ricky

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!