Locking other cells based on values in a cell.

[Mario]

| A| B| C| D|
------------------------------------------
1|Task |Desc |Hrs |Date |
2|Stucco |
3|Paint |
4|Plumb |

Lets say above is my sample spread sheet. How can I
block/lock the cells for col B, Col C and Col D in a row
if the value in Col A of same row is not "Plumb".

In other words if A2 is not equal to "Plumb" then cells
B2, C2 and D2 has to be locked and made not editable or
non-data-enterable.

Thanks
Mario

 

[Guest]

Mario,
This checks the two cells specified in the first IF statement only
You will want to format all cells that you wish to reamain unlocked as the ActiveSheet.Protect affects the entire sheet. The default for a cell is to be locked if the sheet if protected, hence all cells will be protected unless you change the formatting.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$4" Or Target.Address = "$A$5" Then
If Target = "Plumb" Then
ActiveSheet.Unprotect
Target.Offset(0, 1).Cells.Locked = True
Target.Offset(0, 2).Cells.Locked = True
Target.Offset(0, 3).Cells.Locked = True
ActiveSheet.Protect
Else
ActiveSheet.Unprotect
Target.Offset(0, 1).Cells.Locked = False
Target.Offset(0, 2).Cells.Locked = False
Target.Offset(0, 3).Cells.Locked = False
ActiveSheet.Protect
End If
End If
End Sub