Double Click to change color

J

JSJohnson

New Member
Joined
Apr 1, 2019
Messages
3
I am looking to use the RGB values for a specific color (other than those in the 56 color palette) I have tried just using:
Case 45=Target.Interior.Color= RGB(102, 102, 255)
Case RGB(102, 102, 255= Target.Interior.Color= RGB(255, 255, 51)
But when i do this it reaches the first RGB and locks up the cell where i am unable to click colors forward or backward. Help would be appreciated
Below is what I have created thus far: (This code works butonly with the color index. Need to use RGB values for more color variation)

Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("AA1:AB10")) Is Nothing Then
        Cancel = True
        Select Case Target.Interior.ColorIndex
            Case xlnone: Target.Interior.ColorIndex = 56
            Case 56: Target.Interior.ColorIndex = 13
            Case 13: Target.Interior.ColorIndex = 6
            Case 6: Target.Interior.ColorIndex = 44
            Case 44: Target.Interior.ColorIndex = 8
            Case 8: Target.Interior.ColorIndex = 5
            Case 5: Target.Interior.ColorIndex = 4
            Case 4: Target.Interior.ColorIndex = 10
        End Select
    End If
End Sub


Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("AA1:AB10")) Is Nothing Then
        Cancel = True
        Select Case Target.Interior.ColorIndex
            Case 10: Target.Interior.ColorIndex = 4
            Case 4: Target.Interior.ColorIndex = 5
            Case 5: Target.Interior.ColorIndex = 8
            Case 8: Target.Interior.ColorIndex = 44
            Case 44: Target.Interior.ColorIndex = 6
            Case 6: Target.Interior.ColorIndex = 13
            Case 13: Target.Interior.ColorIndex = 7
            Case 7: Target.Interior.ColorIndex = xlnone
        End Select
    End If
End Sub
 
Last edited by a moderator:

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
Can you please show the code that doesn't work?
Also when posting code please use code tags, the # icon in the reply window.
 
Upvote 0
Below is the not woking code
Code: 
<code>
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("Aa1:aB10")) Is Nothing Then
        Cancel = True
        Select Case Target.Interior.Color
            Case xlnone: Target.Interior.Color = RGB(102, 102, 255)
            Case RGB(102, 102, 255): Target.Interior.Color = RGB(102, 0, 255)
            Case RGB(102, 0, 255): Target.Interior.Color = RGB(255, 255, 51)
            Case RGB(255, 255, 51): Target.Interior.Color = RGB(255, 102, 0)
            Case RGB(255, 102, 0): Target.Interior.Color = RGB(255, 0, 51)
            Case RGB(255, 0, 51): Target.Interior.Color = RGB(153, 0, 0)
            Case RGB(153, 0, 0): Target.Interior.Color = RGB(0, 153, 0)
            Case RGB(0, 153, 0): Target.Interior.ColorIndex = RGB(204, 0, 255)
        End Select
    End If
End Sub


Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("A1:B10")) Is Nothing Then
        Cancel = True
        Select Case Target.Interior.Color
            Case RGB(204, 0, 255): Target.Interior.Color = RGB(0, 153, 0)
            Case RGB(0, 153, 0): Target.Interior.Color = RGB(153, 0, 0)
            Case RGB(153, 0, 0): Target.Interior.Color = RGB(255, 0, 51)
            Case RGB(255, 0, 51): Target.Interior.Color = RGB(255, 102, 0)
            Case RGB(255, 102, 0): Target.Interior.Color = RGB(255, 255, 51)
            Case RGB(255, 255, 51): Target.Interior.Color = RGB(102, 0, 255)
            Case RGB(102, 0, 255): Target.Interior.Color = RGB(102, 102, 255)
            Case RGB(102, 102, 255): Target.Interior.Color = xlnone
        End Select
    End If
End Sub</code>
 
Last edited by a moderator:
Upvote 0
Try
Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("AA1:AB10")) Is Nothing Then
        Cancel = True
        Select Case Target.Interior.Color
            Case [COLOR=#ff0000]16777215[/COLOR]: Target.Interior.Color = RGB(102, 102, 255)
            Case RGB(102, 102, 255): Target.Interior.Color = RGB(102, 0, 255)
            Case RGB(102, 0, 255): Target.Interior.Color = RGB(255, 255, 51)
            Case RGB(255, 255, 51): Target.Interior.Color = RGB(255, 102, 0)
            Case RGB(255, 102, 0): Target.Interior.Color = RGB(255, 0, 51)
            Case RGB(255, 0, 51): Target.Interior.Color = RGB(153, 0, 0)
            Case RGB(153, 0, 0): Target.Interior.Color = RGB(0, 153, 0)
            Case RGB(0, 153, 0): Target.Interior.Color = RGB(204, 0, 255)
        End Select
    End If
End Sub
You've also left ColorIndex on the last line
 
Upvote 0
Thank you Fluff that solved that problem, I much appreciate it.
Next Challange:
I have set up the multiple color sequences and locations for each, I have also used worksheetwatcher to utalize multiple subs. The issue I have is that on running the following I get a Compile error: Ambiguous name detected: WorksheetWatcher_BeforeDoubleClick. As i know this is the same name as other sub I am not exactly sure what syntax i can use so that they will all work. Any thoughts would be wonderful.
Code: 
<code>
Private WithEvents WorksheetWatcher As Worksheet
Private Sub Worksheet_Activate()


    Set WorksheetWatcher = Me


End Sub


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("A1:G1")) Is Nothing Then
        Cancel = True
        Select Case Target.Interior.Color
            Case 16777215: Target.Interior.Color = RGB(255, 153, 153)
            Case RGB(255, 153, 153): Target.Interior.Color = RGB(255, 102, 102)
            Case RGB(255, 102, 102): Target.Interior.Color = RGB(51, 255, 153)
            Case RGB(51, 255, 153): Target.Interior.Color = RGB(102, 153, 0)
            Case RGB(102, 153, 0): Target.Interior.Color = RGB(102, 102, 255)
            Case RGB(102, 102, 255): Target.Interior.Color = RGB(102, 0, 255)
            Case RGB(102, 0, 255): Target.Interior.Color = RGB(0, 102, 0)
       End Select
    End If
End Sub


Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("A1:G1")) Is Nothing Then
        Cancel = True
        Select Case Target.Interior.Color
            Case RGB(0, 102, 0): Target.Interior.Color = RGB(102, 0, 255)
            Case RGB(102, 0, 255): Target.Interior.Color = RGB(102, 102, 255)
            Case RGB(102, 102, 255): Target.Interior.Color = RGB(102, 153, 0)
            Case RGB(102, 153, 0): Target.Interior.Color = RGB(51, 255, 153)
            Case RGB(51, 255, 153): Target.Interior.Color = RGB(255, 102, 102)
            Case RGB(255, 102, 102): Target.Interior.Color = RGB(255, 153, 153)
            Case RGB(255, 153, 153): Target.Interior.Color = 16777215
        End Select
    End If
End Sub


Private Sub WorksheetWatcher_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("K1:Q1")) Is Nothing Then
        Cancel = True
        Select Case Target.Interior.Color
            Case 16777215: Target.Interior.Color = RGB(51, 255, 204)
            Case RGB(51, 255, 204): Target.Interior.Color = RGB(255, 255, 51)
            Case RGB(255, 255, 51): Target.Interior.Color = RGB(255, 153, 0)
            Case RGB(255, 153, 0): Target.Interior.Color = RGB(255, 102, 0)
            Case RGB(255, 102, 0): Target.Interior.Color = RGB(255, 0, 51)
            Case RGB(255, 0, 51): Target.Interior.Color = RGB(153, 0, 0)
            Case RGB(153, 0, 0): Target.Interior.Color = RGB(0, 153, 0)
        End Select
    End If
End Sub


Private Sub WorksheetWatcher_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("K1:Q1")) Is Nothing Then
        Cancel = True
        Select Case Target.Interior.Color
            Case RGB(0, 153, 0): Target.Interior.Color = RGB(153, 0, 0)
            Case RGB(153, 0, 0): Target.Interior.Color = RGB(255, 0, 51)
            Case RGB(255, 0, 51): Target.Interior.Color = RGB(255, 102, 0)
            Case RGB(255, 102, 0): Target.Interior.Color = RGB(255, 153, 0)
            Case RGB(255, 153, 0): Target.Interior.Color = RGB(255, 255, 51)
            Case RGB(255, 255, 51): Target.Interior.Color = RGB(51, 255, 204)
            Case RGB(51, 255, 204): Target.Interior.Color = 16777215
        End Select
    End If
End Sub


Private Sub WorksheetWatcher_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("U1:AC1")) Is Nothing Then
        Cancel = True
        Select Case Target.Interior.Color
            Case 16777215: Target.Interior.Color = RGB(204, 0, 255)
            Case RGB(204, 0, 255): Target.Interior.Color = RGB(102, 0, 153)
            Case RGB(102, 0, 153): Target.Interior.Color = RGB(255, 51, 51)
            Case RGB(255, 51, 51): Target.Interior.Color = RGB(153, 0, 51)
            Case RGB(153, 0, 51): Target.Interior.Color = RGB(0, 204, 55)
            Case RGB(0, 204, 55): Target.Interior.Color = RGB(0, 0, 153)
            Case RGB(0, 0, 153): Target.Interior.Color = RGB(255, 102, 153)
            Case RGB(255, 102, 153): Target.Interior.Color = RGB(255, 0, 153)
            Case RGB(255, 0, 153): Target.Interior.Color = RGB(0, 204, 0)
        End Select
    End If
End Sub


Private Sub WorksheetWatcher_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("U1:AC1")) Is Nothing Then
        Cancel = True
        Select Case Target.Interior.Color
            Case RGB(0, 204, 0): Target.Interior.Color = RGB(255, 0, 153)
            Case RGB(255, 0, 153): Target.Interior.Color = RGB(255, 102, 153)
            Case RGB(255, 102, 153): Target.Interior.Color = RGB(0, 0, 153)
            Case RGB(0, 0, 153): Target.Interior.Color = RGB(0, 204, 55)
            Case RGB(0, 204, 55): Target.Interior.Color = RGB(153, 0, 51)
            Case RGB(153, 0, 51): Target.Interior.Color = RGB(255, 51, 51)
            Case RGB(255, 51, 51): Target.Interior.Color = RGB(102, 0, 153)
            Case RGB(102, 0, 153): Target.Interior.Color = RGB(204, 0, 255)
            Case RGB(204, 0, 255): Target.Interior.Color = 16777215
        End Select
    End If
End Sub</code>
 
Last edited by a moderator:
Upvote 0
Firstly, as mentioned before, when posting code please use code tags, the # icon in the reply window.

Secondly, do you want these codes to work on all sheets in the workbook, or just 1?
 
Upvote 0
You must log in or register to reply here.

Similar threads

imback2nite
  • Solved
VBA for if range doesn't equal prescribed ranges.
Replies
2
Views
214
imback2nite
imback2nite
H
  • Question Question
Referencing a series name that contains data linked to a cell VBA
Replies
1
Views
58
Rijnsent
Rijnsent
H
  • Locked
  • Question Question
Help referencing a series name that contains data linked to a cell VBA
Replies
1
Views
36
Fluff
Fluff
A
  • Solved
VBA Case Error
Replies
2
Views
65
Ark68
A
B
  • Question Question
VBA in MacOS to change color of the font in a Shape
Replies
3
Views
271
sijpie
sijpie

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

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