locking and unlocking the excel cells using VBA

[Partha_sai]

Hi All,

I am new to VBA coding. My requirement is to unlock 3 columns when the user select update and to insert a fresh row when they select add. I am able to do that. by my excel has nearly 20,000 records and each and every time when some change occurs I am looping the whole sheet to lock and unlock which is almost taking more than 1 min.
Can any suggest if I can reduce the time its taking . Please find the code which I am using

Public Sub Worksheet_Activate_after()

Sheets("Sheet1").Unprotect
Sheets("Sheet1").Cells.Locked = False
For i = 1 To Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
If Cells(i, 1).Value = "Add" Then
Cells(i, 2).Locked = False

Cells(i, 3).Locked = False

Cells(i, 4).Locked = False

Cells(i, 5).Locked = False

Cells(i, 6).Locked = False

ElseIf Cells(i, 1).Value = "Update" Then
Cells(i, 2).Locked = True
Cells(i, 3).Locked = True
Cells(i, 4).Locked = False
Cells(i, 5).Locked = False
Cells(i, 6).Locked = False

Else
Cells(i, 4).Locked = True

Cells(i, 5).Locked = True

Cells(i, 6).Locked = True
Cells(i, 2).Locked = True
Cells(i, 3).Locked = True

End If

Next
Sheets("Sheet1").Protect
End

End Sub