Vba If cell contains specific word, lock it.

pontiff

Board Regular
Joined
Jun 11, 2009
Messages
143
Office Version
  1. 2016
Hi all,
Looking for some vba that will lock any cell on a sheet containing the text “xxxxx” when I select the protect sheet option. These cells are important as they act as a “target” for some other vba so need to be protected. They also move position.
Thanks in advance
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,499
Office Version
  1. 365
Platform
  1. Windows
Here is one way:
VBA Code:
Sub MyLockMacro()

    Dim myFindString As String
    Dim cell As Range
    Dim lr As Long
    Dim nr As Long
    
'   Indicate value to look for
    myFindString = "xxxxx"

    Range("A1").Select

    Application.ScreenUpdating = False

'   Create loop to look for all instances
    Do
'       Find next instance
        Set cell = Cells.Find(What:=myFindString, After:=ActiveCell, LookIn:=xlFormulas2, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False)
'       Exit loop if cannot find value
        If cell Is Nothing Then
            Exit Do
        Else
'           Capture new row value found on
            nr = cell.Row
'           Check to see if new row is less than previous row
            If nr < lr Then
                Exit Do
            Else
'               Lock found cell
                cell.Activate
                cell.Locked = True
'               Update last cell found
                lr = nr
            End If
        End If
    Loop

    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!"
    
End Sub
 

pontiff

Board Regular
Joined
Jun 11, 2009
Messages
143
Office Version
  1. 2016
Here is one way:
VBA Code:
Sub MyLockMacro()

    Dim myFindString As String
    Dim cell As Range
    Dim lr As Long
    Dim nr As Long
   
'   Indicate value to look for
    myFindString = "xxxxx"

    Range("A1").Select

    Application.ScreenUpdating = False

'   Create loop to look for all instances
    Do
'       Find next instance
        Set cell = Cells.Find(What:=myFindString, After:=ActiveCell, LookIn:=xlFormulas2, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False)
'       Exit loop if cannot find value
        If cell Is Nothing Then
            Exit Do
        Else
'           Capture new row value found on
            nr = cell.Row
'           Check to see if new row is less than previous row
            If nr < lr Then
                Exit Do
            Else
'               Lock found cell
                cell.Activate
                cell.Locked = True
'               Update last cell found
                lr = nr
            End If
        End If
    Loop

    Application.ScreenUpdating = True
   
    MsgBox "Macro complete!"
   
End Sub
Thanks so much for this, I’ll try it out tonight and get back to you. Much appreciated!
 

Forum statistics

Threads
1,144,341
Messages
5,723,808
Members
422,518
Latest member
quack_quack

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top