Protect Unprotect a Worksheet with VBA

When starting a VBA procedure it is often necessary to unlock your worksheet, removing the password.  The following is a typical example.

Sub UnProtectSheet() 'Excel VBA to lock and protect a sheet.
ActiveSheet.Unprotect "password" 'Change Password to suit
'Your Code here
ActiveSheet.Protect "password" 'ReProtect Sheet with "Password"
End Sub

Taking this concept a step further in Excel you can protect certain cells by locking them.  It involves setting the locked property to True.  Someone once asked me to assist them in protecting cells which had Total Acct in Column A.  The number of cells with Total Acct was continually changing and they did not want to keep updating the sheet to account for the increasing amount of cells with these key words.  The following procedure will assist.

Option Explicit

Sub ProtSheet() 'Excel VBA to lock and protect cells.
'Protect certain cells based on condition
Dim Rng As Range
Dim Arng As Range
Set Arng=Range("A11", Range("A" & Rows.Count).End(xlUp))
ActiveSheet.Unprotect "password" 'Change Password to suit
[a10].CurrentRegion.Locked=False
For Each Rng In Arng
If Rng.Value="Total Acct" Then
Rng.Resize(, 6).Locked=True
Else
Rng.Locked=False
End If
Next Rng
ActiveSheet.Protect "password"
End Sub

It looks in Column A for Total Acct and will lock the following 6 Columns.  I presume the user wanted to protect the Sums in these rows so I have created an example VBA file which reflects this.