Toggle Cells

OAM

Board Regular
Joined
Jul 8, 2008
Messages
72
Office Version
  1. 2007
Platform
  1. Windows
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Try this:

Code:
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)
 
Upvote 0
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.
 
Upvote 0
Code:
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
 
Upvote 0
Thank you for all your help, that works good.
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top