I have a protected worksheet that allows users to manually input into
unprotected ranges, or manually copy from a protected range into an
unprotected area. Copy from cells have different font and background colors
that change the destination cell formats after paste even when sheet is
protected.

How do I restore the color formats of the destination cells after the
changes are processed by an executed macro?

The following works fine if sheet is unprotected, but gives error 1004 if
protected:
Range("destname").Interior.ColorIndex = 35
Range("destname").Font.ColorIndex = 1

John,

It's funny that one - you can paste different formats into the cell on a
protected sheet but you can't then change those cell's format!!

Two suggestions - add code to:
- unprotect the sheet
- restore the formats (using your existing code)
- re-protect the sheet

or:
- set up a range on another (hidden?) sheet the same size as your
"destname" range, with the correct cell formats (fonts, borders etc).
- copy this area and paste special just the formats to "destname"


HTH

Tim

"John" wrote in message
...
I have a protected worksheet that allows users to manually input into
unprotected ranges, or manually copy from a protected range into an
unprotected area. Copy from cells have different font and background
colors
that change the destination cell formats after paste even when sheet is
protected.

How do I restore the color formats of the destination cells after the
changes are processed by an executed macro?

The following works fine if sheet is unprotected, but gives error 1004 if
protected:
Range("destname").Interior.ColorIndex = 35
Range("destname").Font.ColorIndex = 1

Tim, I like the unprotect/copy/reprotect option! But I need the code to run
no matter whether the sheet is protected or not so I can debug changes.

How do I tell in a macro whether or not the sheet is protected?
-JHJ

"Tim Barlow" wrote:

John,

It's funny that one - you can paste different formats into the cell on a
protected sheet but you can't then change those cell's format!!

Two suggestions - add code to:
- unprotect the sheet
- restore the formats (using your existing code)
- re-protect the sheet

or:
- set up a range on another (hidden?) sheet the same size as your
"destname" range, with the correct cell formats (fonts, borders etc).
- copy this area and paste special just the formats to "destname"


HTH

Tim

"John" wrote in message
...
I have a protected worksheet that allows users to manually input into
unprotected ranges, or manually copy from a protected range into an
unprotected area. Copy from cells have different font and background
colors
that change the destination cell formats after paste even when sheet is
protected.

How do I restore the color formats of the destination cells after the
changes are processed by an executed macro?

The following works fine if sheet is unprotected, but gives error 1004 if
protected:
Range("destname").Interior.ColorIndex = 35
Range("destname").Font.ColorIndex = 1