Jim Rech Wrote:
That cannot be done through Data, Validation alone since it only affect
the
current cell. A better design is to use a worksheet change event macr
to
make the cells in A and B the opposite whenever one changes. Forge
about
the locking bit; totally unnecessary.

--
Jim
"tahir" wrote in
message ...
|
| Hi Folks,
|
| I have a worksheet that has got 2 Columns, A and B. Both Column
have
| validation list with values YES and No, My query is that for exampl
if
| on cell A10 if i choose Value YES from Validation drop down lis
then
| the cell B10 should automatically Show No and should be locked for
| protection and no changes be made to cell B10. But if i select No in
| A10 then B10 should show YEs and now A10 should be locked.
|
| I have been trying trying and trying but its not happening, Pleas
help
| me on this.
|
| Regards,
|
|
| Tahir
|
|
| --
| tahir
|
| Student, working on a project

------------------------------------------------------------------------
| tahir's Profile:
http://www.excelforum.com/member.php...fo&userid=6053
| View this thread
http://www.excelforum.com/showthread...hreadid=523016
|

This is the code of Worksheet Change, This code works great, but it ha
limitations. The code only triggers on Cell B3 for change. Is i
possible to expand it to a range of A1 to L60

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$3" Then
Application.EnableEvents = False
If Target.Value = "Yes" Then
With Range("C3")
.Value = "No"
With .Validation
.Delete
.Add Type:=xlValidateTextLength, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:="0"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Leave cell blank"
.ShowInput = False
.ShowError = True
End With
End With
Else
With Range("C3")
.Value = "Yes"
With .Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:="=Eligible"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Select from the list"
.ShowInput = False
.ShowError = True
End With
End With
End If
End If
Application.EnableEvents = True
End Su

--
tahi

Student, working on a projec
-----------------------------------------------------------------------
tahir's Profile: http://www.excelforum.com/member.php...nfo&userid=605
View this thread: http://www.excelforum.com/showthread.php?threadid=52301