Avoid Copying Cell Protection Without Macro

lgalster

New Member
Joined
Jun 4, 2009
Messages
6
I need help finding a solution to the following problem!:

I have a range of unlocked cells pinned on the corners by locked cells. Users are allowed to only select unlocked cells. Therefore the user cannot select the first or last row or column (thus nor can they delete, which is the intent), but the user can insert and delete columns at will within the range.

The crux of the problem is that because formats are copied from above or from the left, inserting a row below the first row in the user range or inserting a column before the first column in the user range pins the newly inserted row or column on the outside because it copies the locked cell format from the row above or from the column to the left. How can I avoid that protection from being copied without using a macro?

I could use a macro if it wouldn't erase the undo cache, but my understanding is that it would erase it.

Here's an image that may clarify (or confuse if it doesn't appear properly):

--------------------------------
|Lock| | | | |Lock|
--------------------------------
|Lock|User|User|User|User|Lock|
--------------------------------
| |User|User|User|User| |
--------------------------------
| |User|User|User|User| |
--------------------------------
|Lock|User|User|User|User|Lock|
--------------------------------
|Lock| | | | |Lock|
--------------------------------

I need your help! Thank you!!

-Lance
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Forum statistics

Threads
1,215,061
Messages
6,122,921
Members
449,094
Latest member
teemeren

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top