Macro To Lock Certain Cells... Please Help

Thread starter tahir
Start date Mar 17, 2006

T

tahir

Mar 17, 2006

#1

Hi,

Scenario:
I am working on a sheet that has a column G and Colum H, Column
contains validated list containing values "Yes" and "No", When i choos
"Yes" from any cell in G column the value in the H column adjacent cel
turns to "No", but if i choose "No" from column G the value in H colum
turns "Yes".

Problem:
My problem is related to locking the cell that have a value of "No"
means as soon as the value "No" gets into any cell in Column H th
related cell should be locked for editing. Simply any cell in G or H i
has "No" must be locked. else Open for editing.

Hope Someone will Help,

Regards,

Tahi

T

tahir

Mar 17, 2006

#2

tahir said:
Hi,

Scenario:
I am working on a sheet that has a column G and Colum H, Column
contains validated list containing values "Yes" and "No", When i choos
"Yes" from any cell in G column the value in the H column adjacent cel
turns to "No", but if i choose "No" from column G the value in H colum
turns "Yes".

Problem:
My problem is related to locking the cell that have a value of "No"
means as soon as the value "No" gets into any cell in Column H th
related cell should be locked for editing. Simply any cell in G or H i
has "No" must be locked. else Open for editing.

Currently i m using this code but it do not have locking or protectin
the cells that contain "No" Value.

********Start of Code********
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
If Intersect(Target, Range("G2:G60", "k2:k60")) Is Nothing Then Exi
Sub
Application.EnableEvents = False
If Target.Value = "Yes" Then
Target.Offset(0, 1).Value = "No"
Else
If Target.Value = "No" Then
Target.Offset(0, 1).Value = "Yes"
Else
Target.Offset(0, 1).Value = ""
Application.EnableEvents = False
If Target.Value = "New" Then
Target.Offset(0, 1).Value = "Old"
Else
If Target.Value = "Old" Then
Target.Offset(0, 1).Value = "New"
Else
Target.Offset(0, 1).Value = ""
End If
End If
End If
End If
Application.EnableEvents = True
End Sub
********End of Code********

Hope Someone will Help,

Regards,

Tahir

Please Help Me on this , I m really stuck, This is my third day bu
cant figure out how to lock the cells on selection of values from othe
cell

T

tahir

Mar 17, 2006

#3

tahir said:
Hi,

Scenario:
I am working on a sheet that has a column G and Colum H, Column
contains validated list containing values "Yes" and "No", When i choos
"Yes" from any cell in G column the value in the H column adjacent cel
turns to "No", but if i choose "No" from column G the value in H colum
turns "Yes".

Problem:
My problem is related to locking the cell that have a value of "No"
means as soon as the value "No" gets into any cell in Column H th
related cell should be locked for editing. Simply any cell in G or H i
has "No" must be locked. else Open for editing.

Currently i m using this code but it do not have locking or protectin
the cells that contain "No" Value.

********Start of Code********
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
If Intersect(Target, Range("G2:G60", "k2:k60")) Is Nothing Then Exi
Sub
Application.EnableEvents = False
If Target.Value = "Yes" Then
Target.Offset(0, 1).Value = "No"
Else
If Target.Value = "No" Then
Target.Offset(0, 1).Value = "Yes"
Else
Target.Offset(0, 1).Value = ""
Application.EnableEvents = False
If Target.Value = "New" Then
Target.Offset(0, 1).Value = "Old"
Else
If Target.Value = "Old" Then
Target.Offset(0, 1).Value = "New"
Else
Target.Offset(0, 1).Value = ""
End If
End If
End If
End If
Application.EnableEvents = True
End Sub
********End of Code********

Hope Someone will Help,

Regards,

Tahir

Please Help Me on this , I m really stuck, This is my third day bu
cant figure out how to lock the cells on selection of values from othe
cell

T

tahir

Mar 17, 2006

#4

tahir said:
Hi,

Scenario:
I am working on a sheet that has a column G and Colum H, Column
contains validated list containing values "Yes" and "No", When i choos
"Yes" from any cell in G column the value in the H column adjacent cel
turns to "No", but if i choose "No" from column G the value in H colum
turns "Yes".

Problem:
My problem is related to locking the cell that have a value of "No"
means as soon as the value "No" gets into any cell in Column H th
related cell should be locked for editing. Simply any cell in G or H i
has "No" must be locked. else Open for editing.

Currently i m using this code but it do not have locking or protectin
the cells that contain "No" Value.

********Start of Code********
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
If Intersect(Target, Range("G2:G60", "k2:k60")) Is Nothing Then Exi
Sub
Application.EnableEvents = False
If Target.Value = "Yes" Then
Target.Offset(0, 1).Value = "No"
Else
If Target.Value = "No" Then
Target.Offset(0, 1).Value = "Yes"
Else
Target.Offset(0, 1).Value = ""
Application.EnableEvents = False
If Target.Value = "New" Then
Target.Offset(0, 1).Value = "Old"
Else
If Target.Value = "Old" Then
Target.Offset(0, 1).Value = "New"
Else
Target.Offset(0, 1).Value = ""
End If
End If
End If
End If
Application.EnableEvents = True
End Sub
********End of Code********

Hope Someone will Help,

Regards,

Tahir

Please Help Me on this , I m really stuck, This is my third day bu
cant figure out how to lock the cells on selection of values from othe
cell

T

tahir

Mar 17, 2006

#5

tahir said:
Hi,

Scenario:
I am working on a sheet that has a column G and Colum H, Column
contains validated list containing values "Yes" and "No", When i choos
"Yes" from any cell in G column the value in the H column adjacent cel
turns to "No", but if i choose "No" from column G the value in H colum
turns "Yes".

Problem:
My problem is related to locking the cell that have a value of "No"
means as soon as the value "No" gets into any cell in Column H th
related cell should be locked for editing. Simply any cell in G or H i
has "No" must be locked. else Open for editing.

Currently i m using this code but it do not have locking or protectin
the cells that contain "No" Value.

********Start of Code********
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
If Intersect(Target, Range("G2:G60", "k2:k60")) Is Nothing Then Exi
Sub
Application.EnableEvents = False
If Target.Value = "Yes" Then
Target.Offset(0, 1).Value = "No"
Else
If Target.Value = "No" Then
Target.Offset(0, 1).Value = "Yes"
Else
Target.Offset(0, 1).Value = ""
Application.EnableEvents = False
If Target.Value = "New" Then
Target.Offset(0, 1).Value = "Old"
Else
If Target.Value = "Old" Then
Target.Offset(0, 1).Value = "New"
Else
Target.Offset(0, 1).Value = ""
End If
End If
End If
End If
Application.EnableEvents = True
End Sub
********End of Code********

Hope Someone will Help,

Regards,

Tahir

Please Help Me on this , I m really stuck, This is my third day bu
cant figure out how to lock the cells on selection of values from othe
cell

T

tahir

Mar 17, 2006

#6

tahir said:
Hi,

Scenario:
I am working on a sheet that has a column G and Colum H, Column
contains validated list containing values "Yes" and "No", When i choos
"Yes" from any cell in G column the value in the H column adjacent cel
turns to "No", but if i choose "No" from column G the value in H colum
turns "Yes".

Problem:
My problem is related to locking the cell that have a value of "No"
means as soon as the value "No" gets into any cell in Column H th
related cell should be locked for editing. Simply any cell in G or H i
has "No" must be locked. else Open for editing.

Currently i m using this code but it do not have locking or protectin
the cells that contain "No" Value.

********Start of Code********
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
If Intersect(Target, Range("G2:G60", "k2:k60")) Is Nothing Then Exi
Sub
Application.EnableEvents = False
If Target.Value = "Yes" Then
Target.Offset(0, 1).Value = "No"
Else
If Target.Value = "No" Then
Target.Offset(0, 1).Value = "Yes"
Else
Target.Offset(0, 1).Value = ""
Application.EnableEvents = False
If Target.Value = "New" Then
Target.Offset(0, 1).Value = "Old"
Else
If Target.Value = "Old" Then
Target.Offset(0, 1).Value = "New"
Else
Target.Offset(0, 1).Value = ""
End If
End If
End If
End If
Application.EnableEvents = True
End Sub
********End of Code********

Hope Someone will Help,

Regards,

Tahir

Please Help Me on this , I m really stuck, This is my third day bu
cant figure out how to lock the cells on selection of values from othe
cell

T

tahir

Mar 17, 2006

#7

Please Its Really Getting Hard for me, Please Hel

You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Similar Threads

Locking specific cells	1	Jan 27, 2010
Lock cell if based on value of corresponding cell	1	Mar 31, 2009
Problem sorting a range of columns using VBA	1	Nov 24, 2012
Sumproduct & VBA	4	Aug 9, 2012
Formulas using two cells	8	Aug 8, 2009
Date-time updates every cell?	1	Jun 24, 2009
Prevent range reference	3	Oct 12, 2009
Please help with simple function	1	Aug 7, 2008

Share:

Facebook X Bluesky LinkedIn Reddit Pinterest Tumblr WhatsApp Email Link

Top