Dynamically Protecting Non Blank Cells in a Workshet

[gaurav99]

Happy New year everyone..

I want suggestions on following problem.

I have a workbook whereby I currently protect different worksheets using 'Protect sheet' option.

In the question sheet, there are some users who paste data on daily basis (without removing the old data). I have to manually protect the non blank cells on daily basis to protect the already present data and enable users to paste the data.

Please suggest if any efficient way of doing so. Attaching the excel for reference.

Thanks in advance !

 

[gaurav99]

Adding some more information..

i want to lock the entire range within the pasted data with password. all cells subsequent to last range with data would be available for updating next and thus locked further...

hope I was able to explain.. awaiting inputs from the experts. Thanks

 

[vletm]

@gaurav99
Something like this ... for testing

 

[gaurav99]

Thanks for replying. Attached sheet is on the lines but with some tweaks.

1. Is it possible to define the range to be locked. This may change over the time.

2. Option to unlock, edit & protect it again. (Currently post adding password, it goes into locked mode moment i click on the cell)

Explaining the process in detail:

1. I have two identical sheets used, one acts as local copy while the second sheet is the final copy.

2. I work on my local copy and paste the data in final sheet on daily basis.

3. Another person reviews my final sheet and sometimes suggest changes, which i do in the final sheet itself.

It's for the 3rd step I need to have option to lock, unlock and relock the sheet post making the changes.

Many Thanks for your help..

 

[vletm]

@gaurav99
A1) Yes. You can range, check the code.
A2) You asked: 'Dynamically Protecting Non Blank Cells in a Worksheet.'
If You activated cell and left it blank, the cell protected as You wanted.
>>> new version
I made example range (with code, no need borders!) which works as You wanted ... 'everywhere' else no locking! ... except cell[A1]!
If You click/select cell[A1], it'll change background color.
If black then there is NO protection! If non black then protection works normally.
This works only on the 1st sheet (Sheet1), so You can add as many sheets as You want.
Ideas ... Questions?