Right Click to change color - Help revise

[UMAKEMESIK]

All,

I have this code that when you right click a cell it changes colors. There is an array of colors in the code.

Please , if you can, help updating.

Are there different colors or a list of colors to work with this code?

Can I segregate this code to work only in a range of cells - like D12- d100 or all the way down?

Right now the code works on the entire excel sheet and I accidentally right click on other parts of the page.

Any help would be much appreciated.

Option Explicit

#If VBA7 Then
  Private Declare PtrSafe Function GetKeyState Lib "USER32" (ByVal vKey As Long) As Integer

#End If


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  ' Double-clicking resets interior color of the Target cell
  Cancel = True
  Target.Interior.ColorIndex = xlNone
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
  ' RightClick/CtrlRightClick changes the cell color in forward/reverse order of array a()
  Dim a() As Variant, i As Integer, c As Long, IsCtrl As Boolean

  ' Define array of the colors
  a = Array(vbWhite, vbGreen, vbYellow, vbRed, vbCyan, vbMagenta, vbBlue)

  ' Test if Ctrl key is pressed
  i = GetKeyState(vbKeyControl)
  IsCtrl = i = -127 Or i = -128

  ' Find interior color of the targer cell in a()
  c = Target.Interior.Color
  For i = 0 To UBound(a)
    If c = a(i) Then Exit For
  Next

  ' Exit if color not found in a()
  Cancel = i <= UBound(a)
  If Not Cancel Then Exit Sub

  ' Increase/decrease(if Ctrl) the index 'i' in the colors array a()
  i = i + IIf(IsCtrl, -1, 1)
  If i > UBound(a) Then
    i = 0
  Else
    If i < 0 Then i = UBound(a)
  End If

  ' Set background color
  If i = 0 Then
    Target.Interior.ColorIndex = xlNone
  Else
    Target.Interior.Color = a(i)
  End If

End Sub
[/code}

 

[rollis13]

Add at the beginning of both event macros this line of code:

If Intersect(Target, Range("D12:D100")) Is Nothing Then Exit Sub

 

[DanteAmor]

Are there different colors or a list of colors to work with this code?

Can I segregate this code to work only in a range of cells - like D12- d100 or all the way down?

With the following you have the standard colors from number 3 to 56 (I omitted 1 and 2 because they are black and white)

Propiedad ColorIndex (gráfico de Excel)

Tema de referencia de VBA de Office

learn.microsoft.com

Option Explicit

#If VBA7 Then
  Private Declare PtrSafe Function GetKeyState Lib "USER32" (ByVal vKey As Long) As Integer

#End If

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  ' Double-clicking resets interior color of the Target cell
  If Not Intersect(Target, Range("D12:D" & Rows.Count)) Is Nothing Then
    Cancel = True
    Target.Interior.ColorIndex = xlNone
  End If
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
  Dim n As Long

  If Not Intersect(Target, Range("D12:D" & Rows.Count)) Is Nothing Then
    Cancel = True
    n = Target.Interior.ColorIndex
    If n < 3 Or n = 56 Then n = 3 Else n = n + 1
    Target.Interior.ColorIndex = n
  End If
End Sub

 

[UMAKEMESIK]

Dante,

thanks so much for the quick response and your streamlined code.
works great and as expected.



Only need these colors for now.

If n < 2 Or n = 8 Then n = 2 Else n = n + 1

but, is there a list of proper names that excel would recognize if i wanted to customize my colors but only wanted 7 -10 colors.

with using this line:

a = Array(vbWhite, vbGreen, vbYellow, vbRed, vbCyan, vbMagenta, vbBlue)

i tried vbOrange and it did not work.

 

[UMAKEMESIK]

Add at the beginning of both event macros this line of code:

If Intersect(Target, Range("D12:D100")) Is Nothing Then Exit Sub

thank you for taking the time.
Much appreciated.