Possible to lock formatting only (still allowing data to be altered)?

When you protect the sheet, there is a long list of boxes you can check or uncheck to customize what is protected. Most people fail to notice this step because it is also asking what password to use to protect the sheet.

That is what I get for answering from memory rather than checking first.

Is the "Allow Users To Edit Ranges" option available to you? Meaning, are you using Active Directory?

Ooh, speaking of checking things first...

Try setting the "Locked" property to False on the cells. Then protect the sheet. Formatting is still protected, but the data is not.

Try setting the "Locked" property to False on the cells. Then protect the sheet. Formatting is still protected, but the data is not.

You got my hopes up! I was hoping it would be that easy but when I copy and paste, the formating is still pasted.

Is the "Allow Users To Edit Ranges" option available to you? Meaning, are you using Active Directory?

I was looking at that option a little bit before protecting the sheet but didn't try anything with it yet. When I protect the sheet that option is grayed out.

You apply the "Allow Users to Edit Ranges" settings before you protect your sheet.

Copying and pasting may always be an issue. Even if you prevent formatting changes, the user will not be able to simply paste. The workbook will tell them they cannot edit the cell. They would need to Paste Special as Text Only to avoid errors.

Dang.... I will probably have to make a macro that automatically corrects the formating upon leaving a cell or saving unless someone responds with a better idea. (?)

Where are they copying from? Maybe there is an easier/better way to get the data into the worksheet.

Thank you for the help, Whizbang

Originally Posted by Whizbang

Where are they copying from? Maybe there is an easier/better way to get the data into the worksheet.

It could be from this sheet or another sheet. I found a solution! I had to resort to VB code (which I dread) but I was able to basically copy it and use my existing code!

Created a module and pasted this code (http://www.exceltip.com/st/Row_and_c...Excel/488.html):

Please Login or Register  to view this content.

Then to use the code I used the following on the sheet (just had to add "ShadeAlternateRows Range("A1:F35"), 15, 2" to my existing code):

Please Login or Register  to view this content.