Extend VBA to further columns

[DHAM1963]

Hi, I am not very experienced with VBA so this might be easy. I have the VBA code below which works perfectly for Column B. I know need to do the exact same thing for columns C to AF. Is a nested if statement the way to go. So each column is a separate criteria If B38 is more than 5 lock column B, is C38 is more than 5 lock column C, If D38 is more than 5 lock D. Thanks in advance for any help.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Unprotect "pw"
If Range("B38") < 5 Then
Range("B3:B37").Locked = False
Else
Range("B3:B37").Locked = True
End If
ActiveSheet.Protect "pw"
End Sub

 

[Saurabhj]

Hi,

Please check below code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ActiveSheet.Unprotect "pw"
    For colno = 2 To 32
        If Cells(38, colno) < 5 Then
            Range(Cells(3, colno), Cells(37, colno)).Locked = False
        Else
            Range(Cells(3, colno), Cells(37, colno)).Locked = True
        End If
    Next
    ActiveSheet.Protect "pw"
End Sub

 

[DHAM1963]

Private Sub Worksheet_SelectionChange(ByVal Target As Range) ActiveSheet.Unprotect "pw" For colno = 2 To 32 If Cells(38, colno) < 5 Then Range(Cells(3, colno), Cells(37, colno)).Locked = False Else Range(Cells(3, colno), Cells(37, colno)).Locked = True End If Next ActiveSheet.Protect "pw" End Sub

Thank you so much, this solution worked perfectly, and gives me some code to work off of for some other similar things I want to do.

 

[Saurabhj]

That's Great. Thanks for the feedback.