Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Existing Code to loop thru all w.sheets in wb
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Existing Code to loop thru all w.sheets in wb
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 "Don Guillett" wrote: 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 "BEEJAY" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Really need help with existing code. | Excel Discussion (Misc queries) | |||
making changes to existing sheets | New Users to Excel | |||
Adapt code to loop through sheets | Excel Programming | |||
Using progress bar with existing loop | Excel Programming | |||
Loop across Sheets and number of sheets | Excel Programming |