Protecting data rather than certain cells

[mika.]

Hello,

I locked certain cells on my worksheet, and then protected
the worksheet. However, now I wish to sort the data, but
keep the data that was in the protected cells protected.
Since certain cells are locked and not data, when I
perform a sort, only those same cells are protected,
rather than the data being protected. Is there any way I
can protect the data instead?

Thanks for the help!

 

[mika.]

One way might be to change the background of the cells
containing the data I want locked (call it old data) to
green. Then I enter my new data (that doesn't need to be
protected)on white rows. Then I can sort the data, and
close the spreadsheet.

Every time the spreadsheet opens, I could have a macro
that does the following:

- Checks if the worksheet is protected, and if so
unprotects it
- for all cells, if background is green, lock, else unlock
- reprotect the sheet.

Can anyone see a problem with this? Also, can anyone help
me code the macro or provide me some resources to do so?

Thanks.

 

[Tom Ogilvy]

Dim cell as Range
ActiveSheet.Unprotect
Cells.Locked = False
for each cell in Range("A1").CurrentRegion
if cell.Interior.ColorIndex = 4 then
cell.Locked = True
end if
Next
ActiveSheet.Protect