Group 2 cells like in a userform.

[Emoncada]

I have cells B3 & D3 . One being a "YES" the other a "NO".

Is there a way I can have it say if B3 = "a" then D3 = "" & if "D3" = "a" then B3 = ""

I have this code.

[VBA]Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Range("B3,B5,B7,B9,B11,D3,D5,D7,D9,D11"), Target) Is Nothing Then
Exit Sub

End If

If Target.Value = "a" Then

Target.Value = ""

Else

Target.Value = "a"

End If

Cancel = True

End Sub[/VBA]

I tried this

[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B3") = "a" Then Range("D3") = ""
If Range("D3") = "a" Then Range("B3") = ""
End Sub
[/VBA]

But not working the way I want it to work when B3 = "a" and i double click D3 it stays the same.

[Bob Phillips]

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim OtherCell As String

With Target

If .Address = "$B$3" Then

OtherCell = "D3"
ElseIf .Address = "$D$3" Then

OtherCell = "B3"

Else

Exit Sub
End If

Application.EnableEvents = False

If .Value = "a" Then

Me.Range(OtherCell).Value = ""
ElseIf .Value = "" Then

Me.Range(OtherCell).Value = "a"
End If
End With

Application.EnableEvents = True

End Sub
[/vba]

[RonMcK]

<deleted by author>

[Emoncada]

That's good but can I have it if I double click the one that equals "a" it goes "".

[Bob Phillips]

Goes where? It goes by setting the other.

[Ago]

i think he means like this

[VBA]


Private Sub Worksheet_Change(ByVal Target As Range)
Dim OtherCell As String

With Target

If .Address = "$B$3" Then

OtherCell = "D3"
ElseIf .Address = "$D$3" Then

OtherCell = "B3"

Else

Exit Sub
End If

Application.EnableEvents = False

If .Value = "a" Then

Me.Range(OtherCell).Value = "a"
Me.Range(Target.Address).Value = ""
ElseIf .Value = "" Then

Me.Range(OtherCell).Value = ""
Me.Range(Target.Address).Value = "a"
End If
End With

Application.EnableEvents = True

End Sub


[/VBA]

[mikerickson]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim shortTermMemory As Variant
    If Target.Address = "$B$3" Or Target.Address = "$D$3" Then
        If Target.Value = "a" Then
            Application.EnableEvents = False
            With Range(Target, Range("C3"))
                shortTermMemory = .Value
                Range("b3:d3").Value = vbNullString
                .Value = shortTermMemory
            End With
            Application.EnableEvents = True
        End If
    End If
End Sub

[Emoncada]

Perfect Mikerickson that's exactly what I wanted thanks.