LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 247
Default 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

 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Really need help with existing code. Cam Excel Discussion (Misc queries) 0 August 12th 08 07:14 PM
making changes to existing sheets zeerog New Users to Excel 2 January 2nd 07 11:52 AM
Adapt code to loop through sheets Stuart[_21_] Excel Programming 3 October 7th 05 02:28 AM
Using progress bar with existing loop Jeff Roper[_6_] Excel Programming 1 November 18th 04 05:43 AM
Loop across Sheets and number of sheets Raj[_8_] Excel Programming 2 December 18th 03 09:18 AM


All times are GMT +1. The time now is 12:31 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"