Changing all cell colors when a check box is checked.

UMAKEMESIK

Active Member
Joined
Oct 3, 2005
Messages
371
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.

Code:
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

Active Member
Joined
Oct 3, 2005
Messages
371
thanks for the tip.
works great and not a memory hog

Code:
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.

Code:
Private Sub Worksheet_BeforeleftClick(ByVal Target As Range, Cancel As Boolean)
    Target.Interior.Color = vbWhite
End Sub
and this
Code:
Private Sub Worksheet_BeforeleftClick(ByVal Target As Range, Cancel As Boolean)
    Target.Interior.Color = ""
End Sub
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

UMAKEMESIK

Active Member
Joined
Oct 3, 2005
Messages
371
i found this

Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,930
Office Version
  1. 365
Platform
  1. Windows
You can use this which will limit the event to column E
VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,889
Messages
5,574,846
Members
412,620
Latest member
sharma7s
Top