Speed up code

[rlink_23]

Oh my goodness I have been posting a lot!!! But I am so close to getting my project done and these few last details are making my brain hurt lol... So once I insert this code on my Sheet it goes super slow inputting data from userform to sheet1. Is there a way I can modify it to speed up the process? Or a different way to code it? The reason I have it this way is because I am trying to still change the color of the cell on right click or double click... Not sure how to proceed.

Private Sub Worksheet_Change(ByVal Target As Range)Set Rng = Range("O4:O10003")
If Sheets("sheet1").ProtectContents = True Then Sheets("sheet1").Unprotect Password:="hello"
For Each cell In Rng
 If cell = "" Then
  cell.Locked = False
  Else
  cell.Locked = True
 End If
Next cell
Sheets("sheet1).Protect Password:="hello", UserInterfaceOnly:=True
End Sub


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Interior.ColorIndex = 3
End Sub


Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Interior.ColorIndex = 0
End Sub

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    Set Rng = Range("O4:O10003")
    If Sheets("sheet1").ProtectContents = True Then Sheets("sheet1").Unprotect Password:="hello"
    On Error Resume Next
    ActiveSheet.Range("O4:O10003").SpecialCells(xlCellTypeBlanks).Locked = False
    On Error GoTo 0
    Sheets("sheet1").Protect Password:="hello", UserInterfaceOnly:=True
End Sub

 

[rlink_23]

now its not locking the (O4:010003) Cells after input.... It is much much faster tho!!! lol

 

[mumps]

The Worksheet_Change event is triggered by a change in any cell. That is why the range is being locked after input.

 

[rlink_23]

Its not locking any cells after input

 

[Fluff]

Try adding this line

If Sheets("sheet1").ProtectContents = True Then Sheets("sheet1").Unprotect Password:="hello"
[COLOR=#0000ff]Rng.Locked = True[/COLOR]
On Error Resume Next

 

[rlink_23]

Like a charm!!! Thank you so much!!!!

 

[Fluff]

Glad we could help & thanks for the feedback

 

[rlink_23]

You're quite welcome!! I am sure I will be back lol, as I have a little bit left to do on my project!! This is a wonderful resource for a beginner like me

 

[rlink_23]

******[Solved]*******