Selecting 2 Cells to the right of the ActiveCell

[jammer6_9]

I have a selection based on a data validation where if the value is "O", i want to select 2 cells to the right and protect it else unprotect it.

[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False

If ActiveCell.Value = "O" Then 'is a data validation selection

'How to make below statement in one statement
ActiveCell.Offset(0, 1).Select
ActiveCell.ClearContents
ActiveCell.Offset(0, 1).Select
ActiveCell.ClearContents
'I want to protect the 2 off set cell
Else

'I want to unprotect the 2 off set cell
End If
Application.ScreenUpdating = True

End Sub


[/VBA]

[GarysStudent]

Try:

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim r As Range
Set r = ActiveCell.Offset(0, 1).Resize(1, 2)
If ActiveCell.Value = "O" Then
r.ClearContents
r.Locked = True
Else
r.Locked = False
End If
Application.ScreenUpdating = True
End Sub
[/VBA]

Be aware that you are referencing ActiveCell rather than Target.

[jammer6_9]

Yes I am aware that I am referencing active cell because i dont know where to have a data validation event.

I was having error on this

r.Locked = True

Try:

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim r As Range
Set r = ActiveCell.Offset(0, 1).Resize(1, 2)
If ActiveCell.Value = "O" Then
r.ClearContents
r.Locked = True
Else
r.Locked = False
End If
Application.ScreenUpdating = True
End Sub
[/VBA]

Be aware that you are referencing ActiveCell rather than Target.

[GarysStudent]

If your worksheet is protected, Excel might not let you change the Locked/Unlocked property of a cell. If you need the sheet protected, temporarily un-protect it to change the Locked status.