Toggle Cells

[OAM]

I would like to know if there is a way to toggle an “X” between two cells? Example: B2 would have an X and C2 would not, and if I delete the X in B2 then the X would show up in C2. I would need the range to be B2:C91.
Excel 2007

 

[Scott Huish]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range, o As Long
Set d = Intersect(Target, Range("B2:C91"))
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each c In d
    o = IIf(c.Column = 2, 1, -1)
    If c = "" Then c.Offset(, o) = "X"
Next
Application.EnableEvents = True
End Sub

Copy the code above.
Right click on the sheet tab for the sheet that you want this to happen on.
Click on View Code.
Paste into white area.
Click Alt-q
This code will be saved with the workbook when you save the workbook. (Save as type .xlsm)

 

[OAM]

HOT PEPPER,

Thank you for your response. However, I miss informed you of the columns I need. The correct columns and rows are D3:E89. I tryed changing the line of code to "Set d = Intersect(Target, Range("D2:E91"))" but it does not function correctly after I change it, can you help me make the change?

Sorry for any the bum information and thank you for your help.

 

[Scott Huish]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range, o As Long
Set d = Intersect(Target, Range("D3:E89"))
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each c In d
    o = IIf(c.Column = 4, 1, -1)
    If c = "" Then c.Offset(, o) = "X"
Next
Application.EnableEvents = True
End Sub

 

[OAM]

Thank you for all your help, that works good.