VBA double click colour changing

Jasesair

Board Regular
Joined
Apr 8, 2015
Messages
219
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!
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,016
Office Version
  1. 2016
Platform
  1. Windows
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
 
Solution

Jasesair

Board Regular
Joined
Apr 8, 2015
Messages
219
Office Version
  1. 2016
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?
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,016
Office Version
  1. 2016
Platform
  1. Windows
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 ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,653
Messages
5,626,105
Members
416,161
Latest member
David1966Lewis

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
Top