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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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

H
  • Solved
Assistance needed with VBA code please
Replies
4
Views
360
H100K
H
S
  • Question
VBA Button to turn off a VBA???
Replies
1
Views
289
Fluff
Fluff
I
  • Locked
  • Question
VBA Click Color Follow up
Replies
1
Views
356
Fluff
Fluff
UMAKEMESIK
  • Question
Right Click to change color - Help revise
Replies
4
Views
548
UMAKEMESIK
UMAKEMESIK
L
  • Question
Conditional Formatting vba + Reverting the Conditional Formatting if no data
Replies
1
Views
152
leila
L

Forum statistics

Threads
1,215,521
Messages
6,125,306
Members
449,218
Latest member
Excel Master

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