VBA double click colour changing

Jasesair

Active Member
Joined
Apr 8, 2015
Messages
282
Office Version
  1. 2016
I have the below code which is working well. Currently I'm able to double click through three different colours to highlight cells. I'd like a fourth double click to be blank/clear. Does anyone know how to add this?

Also, I'd rather use RGB colours but am not sure how to change this code to suit that.

VBA Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim rng As Range
Cancel = True
Set rng = ActiveSheet.Range("A1:f1")
If Intersect(Target, rng) Is Nothing Then Exit Sub
Select Case Target.Interior.ColorIndex
Case xlNone, 4: Target.Interior.ColorIndex = 3
Case 3: Target.Interior.ColorIndex = 45
Case 45: Target.Interior.ColorIndex = 4
Case Else: Target.Interior.ColorIndex = xlNone
End Select
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Interior.ColorIndex = xlNone
End Sub

I'm getting close to what I'm wanting but just chasing a little extra help to nail it!
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Can be just like this

VBA Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim rng As Range
Cancel = True
Set rng = ActiveSheet.Range("A1:f1")
If Intersect(Target, rng) Is Nothing Then Exit Sub
Select Case Target.Interior.ColorIndex
    Case xlNone: Target.Interior.ColorIndex = 3
    Case 3: Target.Interior.ColorIndex = 45
    Case 45: Target.Interior.ColorIndex = 4
    Case 4: Target.Interior.ColorIndex = xlNone
End Select
End Sub

If use RGB probably like this

VBA Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim rng As Range
Cancel = True
Set rng = ActiveSheet.Range("A1:f1")
If Intersect(Target, rng) Is Nothing Then Exit Sub
Select Case True
    Case Target.Interior.Color = RGB(255, 255, 255): Target.Interior.Color = RGB(255, 0, 0)
    Case Target.Interior.Color = RGB(255, 0, 0): Target.Interior.Color = RGB(255, 255, 0)
    Case Target.Interior.Color = RGB(255, 255, 0): Target.Interior.Color = RGB(0, 255, 0)
    Case Target.Interior.Color = RGB(0, 255, 0): Target.Interior.Color = RGB(255, 255, 255)
End Select

End Sub
 
Upvote 0
Solution
Thanks so much, Zot! Can I check with you...based on the gridlines, it looks like the clear colour is actually white. Is that the case? Is there any way for it to actually be clear?
 
Upvote 0
Thanks so much, Zot! Can I check with you...based on the gridlines, it looks like the clear colour is actually white. Is that the case? Is there any way for it to actually be clear?
I did not really look at it. Maybe because mostly I just use ColorIndex or jusr vbRed, vbGreen

Actually, using either .Color or .ColorIndex will shade same cell area (including border). Well, border has zero width. So, when I use RGB(255, 255, 255), the whole cell is colored white.

I should have write code like this .... my bad

VBA Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim rng As Range
Cancel = True
Set rng = ActiveSheet.Range("A1:F1")
If Intersect(Target, rng) Is Nothing Then Exit Sub
Select Case True
    Case Target.Interior.Color = RGB(255, 255, 255): Target.Interior.Color = RGB(255, 0, 0)
    Case Target.Interior.Color = RGB(255, 0, 0): Target.Interior.Color = RGB(255, 255, 0)
    Case Target.Interior.Color = RGB(255, 255, 0): Target.Interior.Color = RGB(0, 255, 0)
    Case Target.Interior.Color = RGB(0, 255, 0): Target.Interior.Color = xlNone
End Select

End Sub

Interestingly, if you replace RGB(255, 255, 255) with xlNone in the first case line there it will not work. The xlNone is detected as White still ?

Thanks for the question ;)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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