For Each, Next ws not looping, possible range reference issue

[Nic2]

Hello, I have this macro to unlock and highlight cells in a range that should apply to the same range in each sheet of the workbook. It works great on the active sheet but it isn't looping. I'm guessing it's an issue with the range reference from sheet to sheet but I can't seem to get it, any help is very much appreciated. Thank you

Sub Unlock_and_Highlight()
Dim Pd5 As Range
Dim ws As Worksheet
Set Pd5 = Range("N3:N8,N11:N20")
For Each ws In ThisWorkbook.Worksheets
If ws.Range("N2") = "Yes" Then Pd5.Locked = False
If ws.Range("N2") = "Yes" Then Pd5.Interior.Color = RGB(220, 220, 220)
Next ws
End Sub

 

[MARK858]

Does it work if you move

Set Pd5 = Range("N3:N8,N11:N20")

inside the loop?

Sub Unlock_and_Highlight()
Dim Pd5 As Range
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
  Set Pd5 = ws.Range("N3:N8,N11:N20")
  If ws.Range("N2") = "Yes" Then Pd5.Locked = False
  If ws.Range("N2") = "Yes" Then Pd5.Interior.Color = RGB(220, 220, 220)
Next ws
End Sub

 

[JoeMo]

Your code is looping, but Pd5 is constrained to the activesheet. Try this:

Sub Unlock_and_Highlight()
Dim Pd5 As Range
Dim ws As Worksheet
Set Pd5 = Range("N3:N8,N11:N20")
For Each ws In ThisWorkbook.Worksheets
Set Pd5 = ws.Range("N3:N8,N11:N20")
If ws.Range("N2") = "Yes" Then Pd5.Locked = False
If ws.Range("N2") = "Yes" Then Pd5.Interior.Color = RGB(220, 220, 220)
Set Pd5 = Nothing
Next ws
End Sub

 

[Nic2]

Does it work if you move

Set Pd5 = Range("N3:N8,N11:N20")

inside the loop?

Sub Unlock_and_Highlight()
Dim Pd5 As Range
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
  Set Pd5 = ws.Range("N3:N8,N11:N20")
  If ws.Range("N2") = "Yes" Then Pd5.Locked = False
  If ws.Range("N2") = "Yes" Then Pd5.Interior.Color = RGB(220, 220, 220)
Next ws
End Sub

Thanks Mark that worked great!

 

[Nic2]

Your code is looping, but Pd5 is constrained to the activesheet. Try this:

Sub Unlock_and_Highlight()
Dim Pd5 As Range
Dim ws As Worksheet
Set Pd5 = Range("N3:N8,N11:N20")
For Each ws In ThisWorkbook.Worksheets
Set Pd5 = ws.Range("N3:N8,N11:N20")
If ws.Range("N2") = "Yes" Then Pd5.Locked = False
If ws.Range("N2") = "Yes" Then Pd5.Interior.Color = RGB(220, 220, 220)
Set Pd5 = Nothing
Next ws
End Sub

Hi JoeMo thanks, this worked great also. So I see the difference between this and the code from Mark858 is the "Set PD5 = Nothing" line at the end, what does that do?

 

[JoeMo]

Hi JoeMo thanks, this worked great also. So I see the difference between this and the code from Mark858 is the "Set PD5 = Nothing" line at the end, what does that do?

In essence it removes the current setting for the range object Pd5 for the worksheet ws.Name before moving to the next worksheet. It's not absolutely needed, but some regard setting range objects to Nothing once they are no longer required as good coding practice. If you comment out that line you will see that it makes no difference.