Use of Intersect: Place Value in one cell AND remove from another

[Ronny Hamida]

Hello again all of you VERY helpful people!

I have a macro to add an "X" to certain cells using the Worksheet_Intersect
VBA function. The problem is that I would like to see only 1 "X" at a time,
but the following macro will allow for both cells (C11 and I11) to have an
"X" at the same time.

-----

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("C11,I11")) Is Nothing Then
If Target = vbNullString Then
Target = "X"
Else
Target = vbNullString
End If
End If
End Sub

-----

Can I add something to this where if C11 already has an "X" and they click
on either C11 or I11, it will remove the "X" from C11 and put one in I11?

Thank you!

Ronny

 

[Jim Cone]

Ronny,
Your rules are not completely clear to me.
The following is my interpretation. Give it a try.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("C11,I11")) Is Nothing Then
If Target.Address = "$C$11" Then
If Len(Target.Value) Then
Target.ClearContents
Me.Range("I11").Value = "X"
Else
Me.Range("I11").ClearContents
Target.Value = "X"
End If
Else
Target.Value = "X"
Me.Range("C11").ClearContents
End If
End If
End Sub
'--------------


"Ronny Hamida"
<[email protected]>
wrote in message
Hello again all of you VERY helpful people!

I have a macro to add an "X" to certain cells using the Worksheet_Intersect
VBA function. The problem is that I would like to see only 1 "X" at a time,
but the following macro will allow for both cells (C11 and I11) to have an
"X" at the same time.
-----
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("C11,I11")) Is Nothing Then
If Target = vbNullString Then
Target = "X"
Else
Target = vbNullString
End If
End If
End Sub

 

[Ronny Hamida]

Jim -

This answered my question perfectly! Thank you!