Changing all cell colors when a check box is checked.

[UMAKEMESIK]

Hello to all,

i have the code below.
with some fomatting
i am able to change the cell e10 to green
when the check box is checked.

e10 is the first cell the check boxes start and continue on down the column.
i have a checkbox in each cell of column below 10

i tried to drag copy down but when i check any other check box only e10 turns
green as stated in the code.

without having to manually format all the cells individually in the column.

is there a way i can take the code below to apply to all cells in column E below
10?

thanks in advance.

Sub myCheckBox1()
Application.Run "RedGreen1"
End Sub


Sub RedGreen1()
Application.ScreenUpdating = False

If [E10].Interior.ColorIndex = 2 Then
[E10].Interior.ColorIndex = 10
Exit Sub
End If
If [E10].Interior.ColorIndex = 10 Then
[E10].Interior.ColorIndex = 2
Application.ScreenUpdating = True


End If
End Sub

 

[UMAKEMESIK]

thanks for the tip.
works great and not a memory hog

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Target.Interior.Color = vbRed
End Sub
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Target.Interior.Color = vbGreen
End Sub

But with this code is there a way to remove the color? Once the color is there its locked in.

i tried this but did not work.

Private Sub Worksheet_BeforeleftClick(ByVal Target As Range, Cancel As Boolean)
    Target.Interior.Color = vbWhite
End Sub

and this

Private Sub Worksheet_BeforeleftClick(ByVal Target As Range, Cancel As Boolean)
    Target.Interior.Color = ""
End Sub

 

[UMAKEMESIK]

i found this

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
' This subroutine colors a cell red when double-clicked then clears it when double-clicked again.
' Some values for .ColorIndex are...
' Red = 3, Green = 4, Blue = 5, Yellow = 6, Orange = 45
' Google "VBA color palette" for more colors

' If the cell is clear
If Target.Interior.ColorIndex = xlNone Then

' Then change the background color to red
Target.Interior.ColorIndex = 3

' Else if the cell background color is red
ElseIf Target.Interior.ColorIndex = 3 Then

' Then clear the background
Target.Interior.ColorIndex = xlNone

End If

' This is to prevent the cell from being edited when double-clicked
Cancel = True

End Sub

I added right click but now looking for a third option.
and the left click option does not work.

any thoughts

 

[Fluff]

You can use this which will limit the event to column E

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Target.Column <> 5 Then Exit Sub
   With Target.Interior
      .ColorIndex = IIf(.ColorIndex = 3, xlNone, 3)
      Cancel = True
   End With
End Sub

Not quite sure what you mean by a 3rd option.
For something like this I would use either a double click, or right click event.