A hard one for the Excel Wizards...

[Guest2]

Using Excel 2007.

I have a worksheet - say sheet one

Drag cells A1 - C10 and add all borders. Whilst selected go to format cells > protection > untick locked

In cell D11 - E14 turn off the locked cells then fill all the cells yellow.

Now go to Review > protect sheet > untick locked cells

Now you can only edit the 2 sets of cells with text, this shouldnt change the borders of A1 - C10 or any of the formatting as it is greyed out. Add some text in a yellow box, now copy and paste it into any of boxes A1 - C10.
This ends up copying the yellow formatting and also remove the borders.

How do you get it to just copy the text? Or is this a bug/limitation of Excel 2007

 

[Guest2]

Just realised this can be done using paste special then values, add a shortcut to quickshortcuts for paste value. Could a macro be set up so that ctrl+v works the same as paste values?

I tried creating a macro but only got it to work for a particular cell

 

[Guest2]

This is the actual question im looking to an answer for -

Question
Is there a way to lock the format of a unlocked cell(sheet is protected).Users are Copying/pasting w/o changing paste options.Cells have different shading,which get copied.

 

[wozencl]

I am quite sure there isn't a way of doing it without a macro. If it's only important for a few cells you can just hook the Sheet_Change event and hardcode the format you want.

You'll need to adjust the users' security settings aswell to prevent them getting the option to disable it.