This is the code of Worksheet Change, This code works great, bu
it has 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=52324

Tahir

change:

If Target.Address = "$B$3" Then

to:

If Intersect(Target,Range("A1:L60")) is Nothing Then Exit Sub

and remove the last End If

Regards

Trevor


"tahir" wrote in
message ...

This is the code of Worksheet Change, This code works great, but
it has limitations. The code only triggers on Cell B3 for change. Is it
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 Sub


--
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=523248

I did as u recommended but now i have a scenario, I want to choose Ye
from A1 and The result in B1 shows No thats fine but i want to do th
same treatment to all the cells in A column and B column. is i
possible, follwoing is the code

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("a1:b10")) Is Nothing Then Exit Sub
Application.EnableEvents = False
With Range("a1")
If Target.Value = "Yes" Then
With Range("b1")
.Value = "No"
End With
Else
With Range("b1")
.Value = "Yes"
End With
End If
Application.EnableEvents = True
End With
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=52324

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Target.Value = "Yes" Then
Target.Offset(0,1).Value = "No"
Else
Target.Offset(0,1).Value = "Yes"
End If
Application.EnableEvents = True
End Sub

Regards

Trevor


"tahir" wrote in
message ...

I did as u recommended but now i have a scenario, I want to choose Yes
from A1 and The result in B1 shows No thats fine but i want to do the
same treatment to all the cells in A column and B column. is it
possible, follwoing is the code

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("a1:b10")) Is Nothing Then Exit Sub
Application.EnableEvents = False
With Range("a1")
If Target.Value = "Yes" Then
With Range("b1")
Value = "No"
End With
Else
With Range("b1")
Value = "Yes"
End With
End If
Application.EnableEvents = True
End With
End Sub


--
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=523248

Thanks Trevor and all who supported, One more question b4 i complet
this problem, How is it possible to do the same treatment for Column
and D, also column E and F.

Thanks again

Regards,

Tahi

--
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=52324