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
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,037
Office Version
  1. 365
Platform
  1. Windows
If you are using Form control checkboxes you can assign this code to all of them
VBA Code:
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

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

UMAKEMESIK

Active Member
Joined
Oct 3, 2005
Messages
371

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,037
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

UMAKEMESIK

Active Member
Joined
Oct 3, 2005
Messages
371

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,037
Office Version
  1. 365
Platform
  1. Windows
It's more likely to be the number of objects on the sheet, rather then anything else.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,037
Office Version
  1. 365
Platform
  1. Windows
Depending on what the checkboxes do, you could possibly replace them with double_click event code
 

Watch MrExcel Video

Forum statistics

Threads
1,119,063
Messages
5,575,886
Members
412,689
Latest member
nhsmedic
Top