Click Cell to toggle color

[chuckchuckit]

Here is code to color/uncolor a cell you click. It does not toggle unless you click a different cell first, then come back and click original cell again.

How can I get it to toggle color on/off while remaining in same cell?

Sub Worksheet_SelectionChange(ByVal Target As Range)
 
    Dim cRED, cGREEN
    cRED = 255
    cGREEN = 5296274
    If Intersect(Target, Range("B2:G10")) Is Nothing Then Exit Sub
 
        'Click and drag in range causes run time error
        'so capture error and exit sub instead
    On Error GoTo errTrap
 
    Application.EnableEvents = False
 
        'Toggle background color on or off
    If ActiveCell.Interior.Pattern = xlSolid Then
            'erase background to no color
        With Selection.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    Else 'no color so make it a backround color
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = cGREEN
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End If
 
    Application.EnableEvents = True
 
errTrap:     'code goes here if error clicking and dragging
    Exit Sub
End Sub

 

[chuckchuckit]

GTO - that works really well for the double click, but if I use the single click version with your code, then I do have to exit the cell and return to get it to toggle.

 

[GTO]

GTO - that works really well for the double click, but if I use the single click version with your code, then I do have to exit the cell and return to get it to toggle.

Ehhh?

 

[chuckchuckit]

Sorry. Here is the single click Sub Worksheet_SelectionChange () version using your code:

Sub Worksheet_SelectionChange(ByVal Target As Range)
 
    If Not Application.Intersect(Target, Range("B2:G10")) Is Nothing _
    And Target.Count = 1 Then
 
        Cancel = True
 
        Select Case True
        Case Target.Interior.ColorIndex = xlNone: Target.Interior.Color = 52377
        Case Target.Interior.Color = 52377: Target.Interior.ColorIndex = xlNone
        End Select
    End If
End Sub

I still have to leave the cell and return to get it to toggle. But your code as you wrote it does work for double clicking. Can I modify your code to work for single clicking?

 

[Sandeep Warrier]

SelectionChange is exactly that, you have to change the selection for this event to trigger.

 

[chuckchuckit]

Sandeep - Selection change would explain it. GTO's double click code version works great as it also does not have run time error when I click and drag.

GTO - I wonder if there is some sort of "BeforeSingleClick" type procedure I can use with code you have?

 

[chuckchuckit]

Thanks everyone. That all really helped.

 

[GTO]

The right click version works great on my computer. The events also seem to also be working on my computer. I would just as soon "not" have them active if I were to use the right click.

Is there a way to make the right click events not active in this range?

I am afraid I am not understanding this bit.

Sandeep - Selection change would explain it. GTO's double click code version works great as it also does not have run time error when I click and drag.

GTO - I wonder if there is some sort of "BeforeSingleClick" type procedure I can use with code you have?

In addition to Sandeep's observations (Hi Sandeep ), there is no Cancel arg in SelectionChange.

There is no SingleClick, because selecting a cell would fire it.

 

[chuckchuckit]

Per sandeeps comment, I was assuming that once a cell is selected, the change is then made by "Worksheet_SelectionChange". But once you have selected a cell you can not reselect it again because it "already is" selected if you do not exit.

And perhaps that is why one has to exit the cell and reselect it, to get "Worksheet_SelectionChange" to toggle.

I am assuming that.

 

[Sandeep Warrier]

Per sandeeps comment, I was assuming that once a cell is selected, the change is then made by "Worksheet_SelectionChange". But once you have selected a cell you can not reselect it again because it "already is" selected if you do not exit.

And perhaps that is why one has to exit the cell and reselect it, to get "Worksheet_SelectionChange" to toggle.

I am assuming that.

Yes.

When you exit the cell (by selecting another cell), the other cell (if within range) will also change colours.

For the double click event, you don't have to select another cell. Just press Esc.

In addition to Sandeep's observations (Hi Sandeep ), there is no Cancel arg in SelectionChange.

Hey Mark ... hows things??

 

[chuckchuckit]

This double click code by GTO works well as I do not have to exit cell to toggle color on/off. Just double click twice.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 
    If Not Application.Intersect(Target, Range("B2:G10")) Is Nothing _
    And Target.Count = 1 Then
 
        Cancel = True
 
        Select Case True
        Case Target.Interior.ColorIndex = xlNone: Target.Interior.Color = 52377
        Case Target.Interior.Color = 52377: Target.Interior.ColorIndex = xlNone
        End Select
    End If
End Sub

But there appears to be no similar code that will work for "single click" to toggle color on/off by just single clicking twice. It would have to be called something like "BeforeSelectionChange" and I don't think there is one.





.