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