Alternate between two characters by selecting


New Member
Jul 12, 2018
Hi all,
I found this code to alternate between two characters (I use it to go around the autofill checkbox problem) it works great except it changes all the cells in the column instead of just the selected cell.

P.S. I'm a real noob in coding, so please be specific :)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Fake checkbox
    If Not Intersect(Target, Range("G:G")) Is Nothing Then
        If Target.Count > 1 Then Exit Sub
        If Target.Value = "¨" Then Target.Value = "=CHAR(254)" Else: Target.Value = "=CHAR(168)"
    End If
    'Column skip
    If Not Intersect(Target, [F:F]) Is Nothing Then
        If Target.Count > 1 Then Exit Sub
        Static sRg As Range
        Dim ColumnOffset As Integer
        With Target
            Application.EnableEvents = False
            If Not sRg Is Nothing Then
                If sRg.Column < .Column Then
                    ColumnOffset = 2
                ElseIf .Column <> 1 Then
                    ColumnOffset = -1
                End If
                ColumnOffset = 1
            End If
            .Offset(, ColumnOffset).Select
                    Application.EnableEvents = True
        End With
        Set sRg = ActiveCell
    End If

End Sub

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.


Well-known Member
Nov 1, 2008
This is a bit of obscure code.
If you select a cell in column G then it will do a alternate charcter. (It should not do the complete column!)

If you select a cell in column F for the first time it will select the neighboring cell in column G ( but it should not trigger the swap)
If you select a cell in column F a next time, it will select the neighboring cell in column E.

Do you need any of this last stuff (automatically changing the column)? Or just when you click in column G that it does the toggle action?


New Member
Jul 12, 2018
The ''Column Skip'' portion of the code is used to be able to use TAB to fill in all the information in the table without toggling the ''Checkbox''. it essentially skips over that column, so if you want to toggle the ''Checkbox'' you have to either click the column or press the left arrow until you select the ''Checkbox'' cell.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...