VBA Lock and Protect cells or range of cells

[rluzius]

Corporate edict.

I have a worksheet that is locked and protected now, except for cells in a certain collumn. I have named the cells in that column "MS96A".

If a user enters a date in a cell or range of cells anywhere in the column, the changed cells also need to be locked and protected (Once they enter a date, it is not allowed EVER to be changed again. Corporate requirement! *Shrug*).

What I am looking for is this. If the user selects that cell again, they will get the usual pop-up message, "The cell or chart that you are trying to change is protected..."

I think I am close, but I am getting an "End If without block If" error on the If Clause.


Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim MRange As Range
Set MRange = Range("MS96A")
' If Not Intersect(Target, MRange) Is Nothing Then

For Each cell In MRange

Sheets("Sheet1").Unprotect Password:="temp"
cell.Interior.ColorIndex = 3
cell.Font.Color = vbBlack
Selection.Locked = True
Selection.FormulaHidden = False
​

Next cell
​

ActiveSheet.Protect Password:="temp", _
DrawingObjects:=False, _
Contents:=True, _
Scenarios:=False
ActiveSheet.EnableSelection = xlUnlockedCells
​

End Sub

 

[VoG]

Should it be?

Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim MRange As Range
Set MRange = Range("MS96A")
If Not Intersect(Target, MRange) Is Nothing Then
    For Each cell In MRange
        Me.Unprotect Password:="temp"
        cell.Interior.ColorIndex = 3
        cell.Font.Color = vbBlack
        Selection.Locked = True
        Selection.FormulaHidden = False
    Next cell
End If
Me.Protect Password:="temp", DrawingObjects:=False, contents:=True, Scenarios:=False
Me.EnableSelection = xlUnlockedCells
End Sub

 

[rluzius]

I had tried an End If, but it still wouldn't work.

I just needed to remove the underscore at the end of the If statement.

Thanks Greatly