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

 

[Fluff]

If you are using Form control checkboxes you can assign this code to all of them

Sub RedGreen1()
   With Range("E" & ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row)
      If .Interior.ColorIndex = 2 Then
         .Interior.ColorIndex = 10
      Else
         .Interior.ColorIndex = 2
      End If
   End With
End Sub

 

[UMAKEMESIK]

thanks
tried the code
i am using the form check box.

i drag copied on a test sheet 4 cells down.

when i check the box of any of them
the correct cell truns green

but all the check boxes get checked at the same
time but only the box i checked initially gets the
green color.

i think this has to do with my cell link in format
control as i put in E10

format control --> control --> cell link

i have e10 in here, is this my issue?

any help is appreciated.

 

[Fluff]

i have e10 in here, is this my issue?

Yes, you need to remove the link cell, or change it for each checkbox

 

[UMAKEMESIK]

Worked great on my test page.
i did not apply any cell link

i drag copied all the way down the sheet
and they all checked and changed color
as they should.

thanks for all of your help.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[UMAKEMESIK]

Hello,

just wondering:

This is a memory hog.
I dragged down to 100 cells and it works great

but the saving and scrolling
and opening is severely hampered.

on 5 rows it works great, but once i start to copy drag down
to many rolls the sheet slows down.

Could it be the Code?
Could it be the two colors in the Code - 2 and 10?

is there a way to construct the code that the first color is blank
and the check color is 10

just looking for ways to minimize the lag.

thanks for your time.

 

[Fluff]

It's more likely to be the number of objects on the sheet, rather then anything else.

 

[UMAKEMESIK]

thanks,
it is a super busy sheet

 

[Fluff]

Depending on what the checkboxes do, you could possibly replace them with double_click event code