The problem with that approach is that you're assuming that the cells in the "UsedRange" that do not have an "X" in them have some other value.
If, for example, all of column "A" is blank, and the user places an "X" in column "A" for each row in which he wants to unlock specific cells in that row, this code would first,remove the "X's", then unlock all rows! So, I think we need to go back to the original question... with regard to looping. There are several different kinds of looping methods. the decision on which one to use will primarily be influenced by the specific situation. In this case, Rich, you want to loop through all of your rows, looking for an "X" in a specific column. The simplest form a the loop is: For RowNum = FirstRow to LastRow ... Next RowNum The trick here is to figure out what your FirstRow and LastRow is. There are seveal ways to do this. Now, if you currenlty have 5,000 rows and you know you'll NEVER have more than 5,000 rows, then you can simply loop from RowNum = 1 to 5000. I the number of rows changes, you could ALWAYS just enter a very large number (64,000 for Excel2003, 1,000,000 for 2007-2010) But this is a waste of cpu cycles, since it will be processing a large number of blank rows. Now, many people like to use the technique where you select a cell near the bottom of the sheet, in a column that ALWAYS has a value, then use the Ctrl-UpArrow technique to find the last cell in that colum that has a value. Like: Range("B64000").Select Selection.End(xlUp).Select I personally don't like this technique because it relies on "Selecting" the sheet, and "selecting" cells on the sheet. Any graphical/display interaction takes up a significant amount of CPU time. (it's very inefficient) Instead, I like to combine a couple of other techniques. For instance: using Ctrl-End will take you to the cell in the furthest row/column that has data. Recording a macro to do this results in: ActiveCell.SpecialCells(xlLastCell).Select Now, as I said, I don't like to "select" cells (unless I have to) So, instead I use the .Row or .Column method to determine this last cell: LastRow = ActiveCell.SpecialCells(xlLastCell).Row LastCol = ActiveCell.SpecialCells(xlLastCell).Column This still requires that a cell be selected (like: Range("A1").select) But it doesn't "jump around" the sheet. Personally, the one I like to use is: If you have a column that ALWAYS has data... For instance, Column B is a sequence number which is a REQUIRED field.. Then you can count the non-blank values in that column: RowCnt = Application.worksheetfunction.counta(Range("B:B")) In that case, the Last Row is the number of rows that have a value in column "B". ----------------- so, you've determined the total number of rows, and set up your loop LastRow = Application.worksheetfunction.counta(Range("B:B")) For RowNum = FirstRow to LastRow ... Next RowNum Next, for each of the rows being processed, you need to test the cell with your criteria and perform your action based on the cell value: If (cells(RowNum,"X").value = 5) then Else End If If you record a macro in which you change the range of cells from Y3 to AX3 to "unlocked", you'll find that is uses: Range("Y3:AX3").Select Range("AX3").Activate Selection.Locked = False This can be shortened. You don't need the .activate step, and you don't need to actually "select" the range of cells: so it can be shortened to: Range("Y3:AX3").Locked = False Then you just have to change the row number (3) to your variable (RowNum) like: Range("Y" & RowNum & ":AX" & RowNum).Locked = False The same thing can be done with: Range(Cells(RowNum, "Y"), Cells(RowNum, "AX")).Locked = False If (Cells(RowNum, "X").Value = 5) Then Range("Y" & RowNum & ":AX" & RowNum).Locked = False Else Range(Cells(RowNum, "Y"), Cells(RowNum, "AX")).Locked = True End If putting this all together, you end up with: ================================================================= Option Explicit Sub Toggle_Locks() Dim RowNum, FirstRow, LastRow FirstRow = 1 LastRow = Application.WorksheetFunction.CountA(Range("X:X")) For RowNum = FirstRow To LastRow If (Cells(RowNum, "X").Value = 5) Then Range("Y" & RowNum & ":AX" & RowNum).Locked = False Else Range(Cells(RowNum, "Y"), Cells(RowNum, "AX")).Locked = True End If Next RowNum End Sub ================================================================= (I checked it... in Excel 2010, it processed 100,000 rows in about 15 seconds.) Paul ----------------------------------------- “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley ----------------------------------------- ________________________________ From: Rajan_Verma <rajanverma1...@gmail.com> To: excel-macros@googlegroups.com Sent: Tue, June 26, 2012 11:26:13 AM Subject: RE: $$Excel-Macros$$ For Each loop No need to Loop UsedRange.Replace “X”,”” Set rng=UsedRange.SpecialCells(xlcelltypeblanks) Rng.value=”X” Rng.entireColumn.Locked=True/False (whatever your want) Regards Rajan verma +91 7838100659 [IM-Gtalk] From:excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Richard Sent: 26 June 2012 8:54 To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ For Each loop This one always confuses me, but it is usually simple, so I would appreciate any help. I want to loop through all the rows with data in my worksheet and if the value in a certain cell is equal to x then I want to unprotect selected columns in that row, otherwise I want to reset the protection in those columns in that row. Thank you in advance for any help Rich -- -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com