Help in code: locked / unlocked based on cell

Sian1

Board Regular
Joined
Nov 9, 2009
Messages
57
Quick question, how could I add when Tier 1 or Tier 2 with cell H13 = "RT - No Insurance" (please note this cell is a drop down list), cell K24:L24 will be locked. Appreciate your help. :)


Private Sub worksheet_change(ByVal target As Range)
If target.Address = "$H$10" Then
If target.CountLarge > 1 Then Exit Sub
ActiveSheet.Unprotect
Select Case target.Value
Case "Tier 1", "Tier 2"
Range("H25:H29").Locked = False
Range("K10:K26").Locked = False
Case "Tier 3", "Tier 4"
Range("H25:H29").Locked = True
Range("K10:K26").Locked = True
Range("H13").Value = "RT with NO RI insurance"
End Select
ActiveSheet.Protect
End If
 

Some videos you may like

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.

CsJHUN

Active Member
Joined
Jan 13, 2015
Messages
357
Office Version
  1. 2016
Platform
  1. Windows
Hi Sian1

VBA Code:
Private Sub worksheet_change(ByVal target As Range)
If target.Address = "$H$10" Then
If target.CountLarge > 1 Then Exit Sub
ActiveSheet.Unprotect
Select Case target.Value
Case "Tier 1", "Tier 2"
if Range("H13").Value = "RT - No Insurance" then Range("K24:L24").Locked = True '''added this line
Range("H25:H29").Locked = False
Range("K10:K26").Locked = False
Case "Tier 3", "Tier 4"
Range("H25:H29").Locked = True
Range("K10:K26").Locked = True
Range("H13").Value = "RT with NO RI insurance"
End Select
ActiveSheet.Protect
End If
 

Sian1

Board Regular
Joined
Nov 9, 2009
Messages
57
Hi Sian1

VBA Code:
Private Sub worksheet_change(ByVal target As Range)
If target.Address = "$H$10" Then
If target.CountLarge > 1 Then Exit Sub
ActiveSheet.Unprotect
Select Case target.Value
Case "Tier 1", "Tier 2"
if Range("H13").Value = "RT - No Insurance" then Range("K24:L24").Locked = True '''added this line
Range("H25:H29").Locked = False
Range("K10:K26").Locked = False
Case "Tier 3", "Tier 4"
Range("H25:H29").Locked = True
Range("K10:K26").Locked = True
Range("H13").Value = "RT with NO RI insurance"
End Select
ActiveSheet.Protect
End If
thanks so much :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,567
Messages
5,625,570
Members
416,119
Latest member
JCLLE

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