MrExcel Publishing
Your One Stop for Excel Tips & Solutions

protect row(or cells) based on value of a cell - PLEASE HELP!!

Posted by Jackie mathis on February 06, 2002 7:56 AM

I have an issue that's letting people delete all my data!!!
Can I protect other cells in same row based on the value entered into 1 column? I really need to protect the sheet with a password, but I will not if it causes a prob or I can't do this with password protection.

Thanks for the help guys, I'm really lost trying to do this.


Posted by Derek on February 06, 2002 7:00 PM

Use a custom validation:
Select your entire worksheet.
Go to Data / Validation and select Custom.
In the formula box type =$A1=0
Click the tab Error Alert and type in the message "restricted". Click Okay.
Now Select Column A , go back to Data Validation and click Clear All.
Data can now only be entered into the other columns if column A for that row is either blank or contains 0.
This prevents overtyping but not pasting over or delete. The following event macro will warn off people selecting in any row where a number greater than 0 is typed in Column A of that row. It is not entirely foolproof but it might help.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A" & Target.Row).Value > 0 Then MsgBox "Do not change or delete"
End Sub

Good luck