You also might want to consider using a worksheet.change event to modify the locked status on a given row range when and only when values in column X are changed.
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Asa Rossoff Sent: Saturday, June 30, 2012 6:24 PM To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ For Each loop Hi Richard, Here's my approach: Sub prototype() Const ColumnlarCriteria As String = "X3:X1000<>5" Dim LockRange As Range Dim LockRow() As Variant Dim Row As Long With Sheet1 .Protect userinterfaceonly:=True Set LockRange = .Range("Y3:AX1000") LockRow() = WorksheetFunction.Transpose(.Evaluate(ColumnlarCriteria)) For Row = 1 To LockRange.Rows.Count LockRange.Rows(Row).Locked = LockRow(Row) Next Row End With End Sub You can add logic to determine the used rows if you don't already know what they are. Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Richard Sent: Tuesday, June 26, 2012 8:37 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ For Each loop Thanks for your reply, but I guess I wasn't clear -- condition a ===if the value of cell x3=5 then I want cells y3 through ax3 to be unprotected, otherwise I want them to be protected. condition b ===if the value of cell x3<>5 then the reverse happens and I would to do it for each row with data, x4=7 then condition b is met x5=5 then condition a is met x6=8 then condition b is met and so forth through probably hundreds of rows On Tuesday, June 26, 2012 8:26:13 AM UTC-7, Rajan_Verma wrote: 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 -- 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
