Existing Code to loop thru all w.sheets in wb

[Guest]

The main body of this code is currently operated from an icon, on active
sheet only.
It is lightning fast.
This code is now required elsewhere, to loop thru each worksheet, or,
better yet, thru specified worksheets - lets call them A, B, C, D, E.
Please help.

Sub UnLocked_Cells()
' Non Locked Cells make Yellow
' Locked Cells make "blank" (if not already so before)

Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect ("xxxx")
End If
Next
ActiveWorkbook.Unprotect (["yyyy"])

' The next section is to loop thru the specified worksheets
' ================================

Dim CELL As Range, tempR As Range, rangeToCheck As Range
Cells.Select
' The Following ONE line is an addition to a working Macro
Cells.Interior.ColorIndex = -4142
For Each CELL In Intersect(Selection, ActiveSheet.UsedRange)
If Not CELL.Locked Then
If tempR Is Nothing Then
Set tempR = CELL
Else
Set tempR = Union(tempR, CELL)
End If
End If

Next CELL
If tempR Is Nothing Then
MsgBox "There are no Unlocked cells " & _
"in the selected range."
End
End If

'Select qualifying cells
'TempR.Select
tempR.Interior.ColorIndex = 6 'yellow

' =================================================

End Sub

 

[Don Guillett]

try this idea

Sub loopsheets()
myarray = Array("sheet1", "sheet5")
For Each ws In myarray
MsgBox Sheets(ws).Range("a1")
Next ws
End Sub

 

[Guest]

Don: Thanks so much
I had to break the coding down, as follows, in order to somehow make
it logical for me.
At this point, it seems to cycle nicely thru the cell checking, but after
manually stepping thru for an extended period of time, I can't find any cells
that have had a colour change, on any sheet.
Please look this over and advise what I am doing wrong.

Sub UnLocked_Cells()
' Non Locked Cells make GREEN
' Locked Cells make "blank" (if not already so before)
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect ("xxxx")
End If
Next
ActiveWorkbook.Unprotect (["yyyy"])

Call LoopSheets
End Sub

Sub LoopSheets()
Dim ws As Variant
Dim myarray As Variant
myarray = Array("Contract", "TruckSpec", "Option", "Pricing", "Notes")
For Each ws In myarray
MsgBox Sheets(ws).Range("a1")

Call Green

Next ws

End Sub

Sub Green()
Dim CELL As Range, tempR As Range, rangeToCheck As Range
Cells.Select
Cells.Interior.ColorIndex = -4142
For Each CELL In Intersect(Selection, ActiveSheet.UsedRange)
If Not CELL.Locked Then
If tempR Is Nothing Then
Set tempR = CELL
Else
Set tempR = Union(tempR, CELL)
End If
End If

Next CELL
If tempR Is Nothing Then
MsgBox "There are no Unlocked cells " & _
"in the selected range."
End
End If

'Select qualifying cells
'TempR.Select
tempR.Interior.ColorIndex = 4 'Green
End Sub

try this idea

Sub loopsheets()
myarray = Array("sheet1", "sheet5")
For Each ws In myarray
MsgBox Sheets(ws).Range("a1")
Next ws
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)

The main body of this code is currently operated from an icon, on active
sheet only.
It is lightning fast.
This code is now required elsewhere, to loop thru each worksheet, or,
better yet, thru specified worksheets - lets call them A, B, C, D, E.
Please help.

Sub UnLocked_Cells()
' Non Locked Cells make Yellow
' Locked Cells make "blank" (if not already so before)

Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect ("xxxx")
End If
Next
ActiveWorkbook.Unprotect (["yyyy"])

' The next section is to loop thru the specified worksheets
' ================================

Dim CELL As Range, tempR As Range, rangeToCheck As Range
Cells.Select
' The Following ONE line is an addition to a working Macro
Cells.Interior.ColorIndex = -4142
For Each CELL In Intersect(Selection, ActiveSheet.UsedRange)
If Not CELL.Locked Then
If tempR Is Nothing Then
Set tempR = CELL
Else
Set tempR = Union(tempR, CELL)
End If
End If

Next CELL
If tempR Is Nothing Then
MsgBox "There are no Unlocked cells " & _
"in the selected range."
End
End If

'Select qualifying cells
'TempR.Select
tempR.Interior.ColorIndex = 6 'yellow

' =================================================

End Sub