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

 

[Rick Rothstein]

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

Before we go on, do you realize you cannot do the above directly? Once you have selected a cell (that is what single clicking a cell does), it remains selected until you select a different cell. That means if you click a cell and turn it yellow, you cannot click it again to change it back to uncolored... you would first have to select some other cell (which would turn yellow if not colored, or become uncolored if already yellow) and then, only then, could you click the first cell to remove its color. This is a limitation of how events work in Excel and there is nothing you can do to work around it. A possible alternative could be to use the BeforeRightClick event to toggle the yellow color in a cell... that you can do directly to the same cell without having to do it to an intervening cell. Do you want to stay with the SelectChange event with its choose another cell before being able to toggle the first cell, or does my idea of using a BeforeRightClick event procedure sound better to you?

 

[feelkg211]

Yes I know. I was talking about it in the POV of a regular user of the file I'm working on.

Tried this too which works! :D

[FONT='inherit']Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Target.Interior.ColorIndex = IIf((Target.Interior.ColorIndex = xlNone), 4, xlNone): Cancel = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Target.Interior.ColorIndex = IIf((Target.Interior.ColorIndex = xlNone), 6, xlNone)
End Sub[/FONT]

 

[Rick Rothstein]

[FONT=&]Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Target.Interior.ColorIndex = IIf((Target.Interior.ColorIndex = xlNone), 4, xlNone): Cancel = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Target.Interior.ColorIndex = IIf((Target.Interior.ColorIndex = xlNone), 6, xlNone)
End Sub[/FONT]

Okay, good, then you are all set now, correct?

 

[feelkg211]

Yes, Sir! Thank you for the help. Really gave me a good view

 

[Readshaws]

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

Hi Rick, that's really helpful. Is there a way to apply different colours to different rows/ranges? I have a spreadsheet I am working on and we would like to be able to use this function on 4 rows in our spreadsheet but each row must have a separate colour. Please let me know if you know a way to do this.