How to lock or unlock cells based on values in another cell in Excel?
In some cases, you may need to lock or unlock cells based on values in another cell. For example, you need the range B1:B4 to be unlocked if cell A1 contains value “Accepting”; and to be locked if cell A1 contains value “Refusing”. How can you do to achieve it? This article can help you.
The following VBA code can help you lock or unlock cells based on value in another cell in Excel.
1. Right click the sheet tab (the sheet with cells you need to lock or unlock based on values in another cell), and click View Code from the right-clicking menu.
2. Then copy and paste the following VBA code into the Code window.
VBA code: Lock or unlock cells based on values in another cell
Private Sub Worksheet_Change(ByVal Target As Range) If Range("A1") = "Accepting" Then Range("B1:B4").Locked = False ElseIf Range("A1") = "Refusing" Then Range("B1:B4").Locked = True End If End Sub
3. Press the Alt + Q keys simultaneously to close the Microsoft Visual Basic for Applications window.
From now on, when you enter value “Accepting” into cell A1, the range B1:B4 is unlocked.
When entering value “Refusing” into cell A1, the specified range B1:B4 is locked automatically.
- How to lock all cell references in formulas at once in Excel?
- How to lock or protect cells after data entry or input in Excel?
- How to lock picture/image to or inside cell in Excel?
Best Office Productivity Tools
Supercharge Your Spreadsheets： Experience Efficiency Like Never Before with Kutools for Excel
Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...
Supports Office/Excel 2007-2021 & newer, including 365 | Available in 44 languages | Enjoy a full-featured 30-day free trial.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!