PDA

View Full Version : Selecting 2 Cells to the right of the ActiveCell



jammer6_9
10-21-2012, 08:06 AM
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.


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

GarysStudent
10-21-2012, 08:32 AM
Try:

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


Be aware that you are referencing ActiveCell rather than Target.

jammer6_9
10-21-2012, 11:13 AM
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:

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


Be aware that you are referencing ActiveCell rather than Target.

GarysStudent
10-21-2012, 12:17 PM
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.