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

feelkg211

New Member
Joined
Apr 12, 2017
Messages
30
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:

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
 
Last edited:
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?
 
Upvote 0

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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

Code:
[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]
 
Last edited:
Upvote 0
Code:
[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?
 
Upvote 0
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.
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)...
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 = -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.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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