Many Tried It Byt Can U...

[tahir]

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

 

[Trevor Shuttleworth]

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]

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

 

[Trevor Shuttleworth]

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]

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