Unlocking cells meeting certain conditions

[niki.sc]

I am an unexperienced user when it comes to VBA and hope to get help here.
I have a password protected excel sheet, in which cells in column B6:B89 are unlocked for user input. Columns C6:Z89 contain formulas, that lookup values in another excel sheet (cells c6:z89 are locked for editing)
Example: User inputs value 2 in column B, the formulas in row C2 to Z2 lookup values in the other excel sheet and automatically fill in cells c2:z2.
Since columns C to Z are locked for editing, users cannot make any changes, which I want anyway, WITH 1 EXCEPTION, and here I need help now with the coding:

If a user inputs value 262 into any row in column B6:B89, the corresponding row should then unlock cells in columns C to Z for editing and highlight this row (only rows which contain value 262 in column B, should unlock cells in columns c:Z, all others rows should remain locked)

I found a similar question in your forum, which I tried and made it work, HOWEVER, the code only works on the first row (guess, because target range is set to cell "B6"). Can someone suggest how to change the code, so that it would also unlock for example row C70:Z70, if a user inputs value 262 in column B70.

And finally, how could I make this code work in every sheet of my workbook without having to enter the code for each worksheet separately.

Here is the code, that I found in the forum and slightly adapted (as mentioned above, it only works for B6; if I enter value 262 in row B8, it does not unlock cells C8:Z8.

current code:

Please Login or Register  to view this content.

Thank you so much for you input and help!

[antoka05]

Insert a module (for example 'module1') in your workbook, then paste the code:

Please Login or Register  to view this content.

And then, for each sheet, paste this code to call the sub presents in the module:

Please Login or Register  to view this content.

Regards,
Antonio

[niki.sc]

Dear Antonio
thank you very much indeed for the code. Unfortunately, I receive following error message:
Compile error - invalid outside procedure
and the first line in the module (With ThisWorkbook.Sheets(Sh)
) is highlighted.

Could you please re-construct the code, so that it executes only on th sheet to which I copy it (I assume, that I copy the sheet it will also copy the code with it). Applying the code automatically to all sheets in a workbook might not be such a good idea in the end, because I might to add other sheets that should remain unprotected.
Thanks! Niki

[antoka05]

See the attached file... I hope it can help you.

Regards,
Antonio

[niki.sc]

See the attached file... I hope it can help you.

Regards,
Antonio

Dear Antonio,
thank you so much - the code works well.
There is one glitch though - if I need to make edits to the worksheet, I have to re-enter the password after each single cell edit (because the code automatically applies the password protection after each entry). This is somewhat tideous and time consuming. The "allow users to edit ranges" also does not allow me to edit several cells in one go, probably because the code in the module executes after each cell entry.

Would there be a workaround with having 2 different passwords during opening the file. One password for general users, which runs the code you wrote, and another password for the administrator, which does not execute your code and lets me edit all ranges in the worksheet without having to re-type the password after each cell edit?

Thanks indeed for your kind assistance, patience and help and I look forward to hear from you again! Niki

[niki.sc]

Hi Antonio,
I tested your code and I found following problem:
A clever user could unlock the cells by typing the ID (262) into column B, which unlocks columns C:Z in that row, then edit the cells and then re-enter another ID, which then would remove all formatting from the cells, which previously warned me that someone overwrote the lookup values and made manual entries in the cells.

I am now considering a different approach with "conditional formatting" which highlights an entire row (e.g. B6:Z6), if the lookup formulas in any column in c6:z6 are overwritten by manually entered values.
Can someone please help me with coding the condition in vba?
Thanks!