VBA to change cell color when double clicked and then change it back when double clicked again

[feelkg211]

Hi all! My current code changes cell color to yellow with a single click and then changes it back to nothing when clicked again. I was wondering if I can do that this time changing it to green when double clicked and then back to nothing when double clicked again. Here's my current code:

Private Sub Worksheet_BeforeDoubleClick(ByVal target As Range, Cancel As Boolean)
    Cancel = True
    Worksheet_SelectionChange target
End Sub

Private Sub Worksheet_SelectionChange(ByVal target As Range)If Intersect(target, Range("B9:AF129")) Is Nothing Then Exit Sub
    'If the target cell is clear
    If target.Interior.ColorIndex = xlNone Then


        'Then change the background to the specified color
        target.Interior.ColorIndex = 6


        'But if the target cell is already the specified color
        ElseIf target.Interior.ColorIndex = 6 Then


        'Then clear the background color
        target.Interior.ColorIndex = xlNone
    End If
End Sub

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("B9:AF129")) Is Nothing Then
If Target.Cells.Count > 1 Then Exit Sub
Cancel = True
    If Target.Interior.ColorIndex = xlNone Then
        Target.Interior.ColorIndex = 4
    Else
       If Target.Interior.ColorIndex = 4 Then Target.Interior.ColorIndex = xlNone
    End If
End If
End Sub

 

[Rick Rothstein]

Try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("B9:AF129")) Is Nothing Then
[B][COLOR="#FF0000"]If Target.Cells.Count > 1 Then Exit Sub[/COLOR][/B]
Cancel = True
    If Target.Interior.ColorIndex = xlNone Then
        Target.Interior.ColorIndex = 4
    Else
       If Target.Interior.ColorIndex = 4 Then Target.Interior.ColorIndex = xlNone
    End If
End If
End Sub

You can eliminate the line of code I highlighted in red above... it is not possible for Target to reference more than a single cell within the BeforeDoubleClick event (you cannot double click two cells a the same time). Also, you can collapse your If..Then..Else..EndIf block down to a single line of code.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Not Intersect(Target, Range("B9:AF129")) Is Nothing Then
    Cancel = True
    Target.Interior.ColorIndex = 4 + xlNone - Target.Interior.ColorIndex
  End If
End Sub

Note: If you want to save Excel the trouble of calculating 4+xlNone every time the event is executed, you can substitute -4138 for it (although that will make it more difficult to understand how the code line works if you do that)...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Not Intersect(Target, Range("B9:AF129")) Is Nothing Then
    Cancel = True
    Target.Interior.ColorIndex = -4138 - Target.Interior.ColorIndex
  End If
End Sub

 

[feelkg211]

How do I combine this with the single click to make it yellow code?

 

[Rick Rothstein]

How do I combine this with the single click to make it yellow code?

That changes things a little. In order to give you the code you need, I need to know what you want to happen in the following two situations...

1) The cell is already green and you select it... does it turn yellow or stay greeen?

2) The cell is already yellow and you double click it... does it turn green or stay yellow?

 

[feelkg211]

Initially, all cells have no highlight.

Single clicking a cell will will highlight it yellow. Single clicking it again turns back to no color.
Double clicking a cell will will highlight it green. Double clicking it again turns back to no color.

If a cell is already yellow, double clicking it would make it green.
If a cell is already green, single clicking it would make it yellow.

Thanks in advance :D

 

[My Aswer Is This]

Did the script I provided do what you want in your original request?

How do I combine this with the single click to make it yellow code?

 

[My Aswer Is This]

Your adding a lot to your original request. And now that Rick is helping you I will move on.

 

[My Aswer Is This]

Yes I know that line of code is not needed now that I think of it. And I like writing code I understand and I know you will always have another way. Thanks.

You can eliminate the line of code I highlighted in red above... it is not possible for Target to reference more than a single cell within the BeforeDoubleClick event (you cannot double click two cells a the same time). Also, you can collapse your If..Then..Else..EndIf block down to a single line of code.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Not Intersect(Target, Range("B9:AF129")) Is Nothing Then
    Cancel = True
    Target.Interior.ColorIndex = 4 + xlNone - Target.Interior.ColorIndex
  End If
End Sub

Note: If you want to save Excel the trouble of calculating 4+xlNone every time the event is executed, you can substitute -4138 for it (although that will make it more difficult to understand how the code line works if you do that)...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Not Intersect(Target, Range("B9:AF129")) Is Nothing Then
    Cancel = True
    Target.Interior.ColorIndex = -4138 - Target.Interior.ColorIndex
  End If
End Sub

 

[feelkg211]

It worked for the most part! But it missed out on the retaining the yellow highlight. Which is what Rick is helping out with :D

I didn't add any requests, just elaborating my original post.

Thank you :D