If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 Lock Row (A~F) based on criteria in g
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Lock Row (A~F) based on criteria in g

#1
July 14th 12, 10:48 AM
 nhkhurum Junior Member First recorded activity by ExcelBanter: Jul 2012 Posts: 1
Lock Row (A~F) based on criteria in g

I have lot of data in sheet 1 (for whole years) I want to lock cell from A~F when in cell G i give some criteria Like Posted. Whole the sheet remain unprotected.

Regards

Noor
#2
July 14th 12, 07:00 PM posted to microsoft.public.excel.programming
 Auric__ external usenet poster Posts: 304
Lock Row (A~F) based on criteria in g

nhkhurum wrote:

> I have lot of data in sheet 1 (for whole years) I want to lock cell from
> A~F when in cell G i give some criteria Like Posted. Whole the sheet
> remain unprotected.

To lock all of the cells in A:F in the current row based on the contents of G
[current row], it's like this:

Sub lockWhenCriteriaMet1()
Dim cell As Range
For Each cell In Range("G:G")
If cell.Value = criteria Then
Range("A" & cell.Row & ":F" & cell.Row).Locked = True
End If
Next
End Sub

If the cells are locked individually, based on G, then it's more like this:

Sub lockWhenCriteriaMet2()
Dim cell As Range
For Each cell In Range("G:G")
Select Case cell.Value
Case criteria_A
Cells(cell.Row, 1).Locked = True
Case criteria_B
Cells(cell.Row, 2).Locked = True
'etc...
End Select
Next
End Sub

--
Sacrificing minions: is there any problem it CAN'T solve?

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post Query criteria based on a changing criteria list bwilk77 Excel Discussion (Misc queries) 2 May 27th 09 04:03 PM Need to lock data based on '=today()' value D Patel[_2_] Excel Programming 0 March 3rd 08 09:10 PM lock cells based on formula MIke Excel Programming 4 November 15th 07 07:31 PM lock cell based on a condition Amanda Excel Worksheet Functions 22 June 25th 07 07:53 PM How to lock a row in a spreadsheet based on the value in a cell Prasad Excel Worksheet Functions 3 December 16th 05 05:18 PM

All times are GMT +1. The time now is 06:52 AM.