(Solved) – Pass in a range and find if the cell values are ‘yes’ or ‘no’ protect and unprotect and add or remove dropdownlist and color

  • by
(solved)-–-pass-in-a-range-and-find-if-the-cell-values-are-‘yes’-or-‘no’-protect-and-unprotect-and-add-or-remove-dropdownlist-and-color

I want to be able to pass in the range RngOP into the method ChangeCode(ByVal Target As Range), If a value cells in that range is No then I remove the dropdownlist and protect it and if No if add the dropdownlist and unprotect it. Is there a way of improving this code please? Please do let me know.

Dim RngOP As Range
Dim sh As Worksheet
Set sh = ActiveWorkbook.Worksheets("Detailed Selection")
lastRow = Cells(Rows.Count, "M").End(xlUp).Row
Set RngOP = Range("P6:P" & lastRow)
'For Each cell In RngOP
'Call ChangeCode(cell)
'Next cell

Sub ChangeCode(ByVal Target As Range)
On Error Resume Next


Dim lastRow As Long
lastRow = Cells(Rows.Count, "M").End(xlUp).Row

 'Check if Target cell in the "Make a selection" range is changed
    If Not Intersect(Target, Range("P6:P" & ActiveSheet.UsedRange.Rows.Count)) Is Nothing Then

        If Target.Value = "Yes" Then
         ActiveSheet.Unprotect Password:=dsPassword 'ARS
            'Dropdown and error message on cells 2 and 3 columns left of "Make a selection" will be enabled


            With Cells(Target.Row, Target.Column   1)

                   .Interior.Color = RGB(255, 255, 255)
                With .Validation
                .InCellDropdown = True
                .ShowError = True
                End With

            End With

              With Cells(Target.Row, Target.Column   2)

                   .Interior.Color = RGB(255, 255, 255)
                With .Validation
                .InCellDropdown = True
                .ShowError = True
                End With

            End With


            Target.Locked = False           
            Cells(Target.Row, Target.Column   1).Locked = False
            Cells(Target.Row, Target.Column   2).Locked = False
           ActiveSheet.Protect Password:=dsPassword  'Contents:=True, DrawingObjects:=False  'ARS

        ElseIf Target.Value = "No" Then

            ActiveSheet.Unprotect Password:=dsPassword 'ARS
            'Dropdown and error message on cells 2 and 3 columns left of "Make a selection" will be enabled


            With Cells(Target.Row, Target.Column   1)
                   '.Interior.Color = RGB(200, 200, 200)
                   .Interior.Color = RGB(221, 217, 196)
                   .Value = vbNullString
                With .Validation
                .InCellDropdown = False
                .ShowError = False
                End With

            End With

             With Cells(Target.Row, Target.Column   2)
                   '.Interior.Color = RGB(200, 200, 200)
                   .Interior.Color = RGB(221, 217, 196)
                   .Value = vbNullString
                With .Validation
                .InCellDropdown = False
                .ShowError = False
                End With

            End With


             Target.Locked = False
             Cells(Target.Row, Target.Column   1).Locked = True
             Cells(Target.Row, Target.Column   2).Locked = True

             ActiveSheet.Protect Password:=dsPassword  'Contents:=True, DrawingObjects:=False  'ARS

        End If
    End If

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *