I am trying to test for "R" in any cell that was changed, if so it remains
unlocked all others should be locked. I have tried all sorts of items and
always get a Error 13 type mismatch. What am I doing worng? Please Help

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sPWORD As String = "36"
Dim rArea As Range
Dim rCell As Range

Me.Unprotect Password:=sPWORD
For Each rArea In Target
For Each rCell In rArea
With rCell
If Target.Value = Not "R" Then
.Locked = Not IsEmpty(.Value)
Else
End If
End With
Next rCell
Next rArea
Me.Protect Password:=sPWORD
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sPWORD As String = "36"
Dim rArea As Range
Dim rCell As Range

Me.Unprotect Password:=sPWORD
For Each rArea In Target
With rArea
If not .Value = "R" Then
.Locked = Not IsEmpty(.Value)
End If
End With
Next rArea
Me.Protect Password:=sPWORD
End Sub

"PhilosophersSage" wrote:

I am trying to test for "R" in any cell that was changed, if so it remains
unlocked all others should be locked. I have tried all sorts of items and
always get a Error 13 type mismatch. What am I doing worng? Please Help

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sPWORD As String = "36"
Dim rArea As Range
Dim rCell As Range

Me.Unprotect Password:=sPWORD
For Each rArea In Target
For Each rCell In rArea
With rCell
If Target.Value = Not "R" Then
.Locked = Not IsEmpty(.Value)
Else
End If
End With
Next rCell
Next rArea
Me.Protect Password:=sPWORD
End Sub

Thank you! Still new to VBA thanks for helping me get things right!

"Luke M" wrote:

'This line is incorrect:
If Target.Value = Not "R" Then

'Change to:
If Not (Target.Value = "R") Then

Do note that nowhere in your macro does it have the ability to unlock the
cell incase it has changed back to R. Is this a problem?
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"PhilosophersSage" wrote:

I am trying to test for "R" in any cell that was changed, if so it remains
unlocked all others should be locked. I have tried all sorts of items and
always get a Error 13 type mismatch. What am I doing worng? Please Help

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sPWORD As String = "36"
Dim rArea As Range
Dim rCell As Range

Me.Unprotect Password:=sPWORD
For Each rArea In Target
For Each rCell In rArea
With rCell
If Target.Value = Not "R" Then
.Locked = Not IsEmpty(.Value)
Else
End If
End With
Next rCell
Next rArea
Me.Protect Password:=sPWORD
End Sub

'This line is incorrect:
If Target.Value = Not "R" Then

'Change to:
If Not (Target.Value = "R") Then

Do note that nowhere in your macro does it have the ability to unlock the
cell incase it has changed back to R. Is this a problem?
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"PhilosophersSage" wrote:

I am trying to test for "R" in any cell that was changed, if so it remains
unlocked all others should be locked. I have tried all sorts of items and
always get a Error 13 type mismatch. What am I doing worng? Please Help

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sPWORD As String = "36"
Dim rArea As Range
Dim rCell As Range

Me.Unprotect Password:=sPWORD
For Each rArea In Target
For Each rCell In rArea
With rCell
If Target.Value = Not "R" Then
.Locked = Not IsEmpty(.Value)
Else
End If
End With
Next rCell
Next rArea
Me.Protect Password:=sPWORD
End Sub