Highlighting multiple cells in multiple locked worksheets

[andypbarker]

Hi

I have a workbook that contains several worksheets that are identical in terms of layout, the user completes the relevant data entry at the end of each week for each of the worksheets, eg dept 1, dept 2 etc to a total sheet. In order to clarify which week has been amended the user then highlights the rows for the week they have been working on and using the fill button then colours the row on each worksheet in the workbook.

The worksheet and the the entire workbook are password protected and the user is only able to select unlocked cells, format cells, format rows, format columns and edit objects.

If the user selects a single worksheet, selects the first unlocked cell in the row and then whilst holding the shift key down selects the last unlocked cell in the row they are able to fill all cells between the two points with a colour.

If, however the user selects the first worksheet then whilst holding the shift key down selects the last worksheet (thereby grouping the worksheets together) and then selects the first cell and the holds the shift key down and selects the last sheet Excel does what you expect to this point and highlights the selected row on all the worksheets, the problem happens if they then try to fill the selected cells, the error message tells them that they cannot use this command on a protected sheet and that they need to unprotect the sheet.

Does anyone know how to get round this

This is also posted on MrExcel - same username - no replies

Thanks

[Leith Ross]

Hello Andypbarker,

Protecting a Workbook or Worksheet doesn't just prevent the user from making changes, it also prevents you, the programmer, from making changes also.

If you want to make changes to protected Worksheets, you must first Unprotect the Worksheet, and then Protect it again when you are done. This has to be done on sheet by sheet basis.

Examples:

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[andypbarker]

It's not actually me that wants to make changes. I want the user to be able to highlight the rows they have been working on when they have finished. Obviously I don't want them to unlock the worksheets.

I have some code that both locks and unlocks each worksheet in turn for the whole workbook

Please Login or Register  to view this content.

And

Please Login or Register  to view this content.

I could insert this as a macro, ask the user to hit a command button when they have finished and run the code to unprotect all the sheets, highlight the relevant rows and then reprotect all the sheets but don't know how to identify which line needs to highlited (It changes every week)

Regards

Andy