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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

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
55,246
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,126,977
Messages
5,621,935
Members
415,867
Latest member
mauroccs

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
Top