Lock column ranges based on value in header row, loop through all

[Nic2]

Hi, I need some help getting this macro to loop. I have a Budget Forecasting workbook with about 40 sheets and I need to unlock cells where the final users will input projections. Below is a snip of the ranges involved. If the value in Row 6 of the column is "Projected" then the cells in rows 7-12 & 15-24 in those columns need to be unlocked. There are 2 sheets in the workbook that need to be excluded, they are named "Consolidation" & "Reference". When I run the macro, it works only on the particular sheet I happen to have active at the time, it won't loop. The macro I'm using is below. Any help is appreciated, thank you in advance!

Sub Unlock()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Consolidation" And ws.Name <> "Reference" Then
If Range("J6") = "Projected" Then
Range("J7:J12,J15:J24").Locked = False
Else
Range("J7:J12,J15:J24").Locked = True
End If

If Range("K6") = "Projected" Then
Range("K7:K12,K15:K24").Locked = False
Else
Range("K7:K12,K15:K24").Locked = True
End If

If Range("L6") = "Projected" Then
Range("L7:L12,L15:L24").Locked = False
Else
Range("L7:L12,L15:L24").Locked = True
End If
End If
Next ws
End Sub

 

[JustJohn91]

I could be mistaken but it sounds like the code is running on the one sheet. Try the below to see if it will update the active sheet.

Sub UnlockCells()

Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        Sheets(ws).Activate
        If ws.Name <> "Consolidation" And ws.Name <> "Reference" Then
            If ActiveSheet.Range("J6") = "Projected" Then
                ActiveSheet.Range("J7:J12,J15:J24").Locked = False
            Else
                ActiveSheet.Range("J7:J12,J15:J24").Locked = True
            End If

            If ActiveSheet.Range("K6") = "Projected" Then
              ActiveSheet.Range("K7:K12,K15:K24").Locked = False
            Else
              ActiveSheet.Range("K7:K12,K15:K24").Locked = True
            End If

            If ActiveSheet.Range("L6") = "Projected" Then
                ActiveSheet.Range("L7:L12,L15:L24").Locked = False
            Else
                ActiveSheet.Range("L7:L12,L15:L24").Locked = True
            End If
        End If
    Next ws
End Sub