Excel 2007 will not let me find and replace data in a protected worksheet. I have ensured that the cell ranges I am trying to change are unlocked.

All the above answers ASSUME that the protection can be removed or bypassed.

But what if your workbook is protected by someone else and you don't have the password to remove the protection or permissions to edit macros?

For this, a simple and easy solution is to copy the unprotected data or entire sheet into a new blank Excel workbook or new unprotected sheet (if it's just one column, you could even use Notepad). Then use the Find/Replace function in the new workbook.

Once you have completed all the Find/Replace you need to do, copy the modified data and paste back into the unprotected range in the protected sheet or workbook.

I agree that it's ridiculous that Excel does not allow you to find/replace in unprotected cells even if the sheet is protected.

Thanks for the help with the macro.

I found I had to do the same thing with insert a row or delete a row, even though Allow Row Insert and Allow Row Delete options were selected.

Microsoft really should do something about making protection more effective.

That I have to write a macro to find and replace in unlocked cells on a protected sheet is ridiculous.

Hi Shane,

thanks for that it works like a charm!!!!!!

kind regards

Leigh

Hi,

looks like your worksheet is protected, go to Review, unprotect sheet

then before protecting again, select the cell where you will allow changes, right click on the mouse, cell format, protection, uncheck box that says Locked, then protect the sheet again