VBA Code to Match US Region to the State that is entered

[Paul_Hossler]

1. I put the protect subs on mod_Util since you have to run them manually and added the CA-CL write capability, marked with <<< below so that you can change it

Sub ProtectMDS()

    With Worksheets("MDS Equipment Detail")
        If .ProtectContents Then Exit Sub
        Range(.Rows(1), .Rows(6)).Locked = True
        Range("CA5").Resize(2, 16).Locked = False       '<<<<<<<<<<<<<<<<<<<
        Range(.Rows(7), .Rows(.Rows.Count)).Locked = False
        .Protect Password:="password", UserInterfaceOnly:=True, _
            DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, _
            AllowDeletingRows:=True, AllowFiltering:=True
        .EnableSelection = xlUnlockedCells
    End With
End Sub
 
 
Sub UnProtectMDS()
    With Worksheets("MDS Equipment Detail")
        If Not .ProtectContents Then Exit Sub
        .Unprotect Password:="password"
    End With
End Sub

2. You must have changed the layout of MOST so the header row was wrong

Public Const rowHeaderMOST As Long = 4
Public Const rowDataStartMOST As Long = 5

3. I changed the logic to not use the UsedRange on MDS but only down to the last row that has a Client