Program to change a cell box to a certain color

Hi,

In Excel, I'd like to be able to click on a check box and have the cell that the checkbox is on turn a certain color.  For example, I have 4 cell boxes A1-D1 with check boxes added in the middle of each cell.  I'd like to be able to click on the checkbox on A1 and have that cell turn green, then on B1 and that cell turn yellow, then on C1 turn Red and D1 also turn red.  Then I'd like to also be able to click on each cell after they've turned their colors and have it go back to it's original white cell.  

Thanks
nelson97Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
DaveConnect With a Mentor Commented:
Hi nelson97,

If you drew the Checkboxes in A1-D1 from the Control Toolbox toolbar, viewable from
View |Toolbars|Control Toolbox

Then doubleclick the Checkbox in A1, and enter the code below

Private Sub CheckBox1_Click()
    If CheckBox1.Value = True Then
        Range("a1").Interior.Color = vbGreen
    Else
        Range("a1").Interior.ColorIndex = xlNone
    End If
End Sub

Repeat the code for the other three checkboxes changing the vbGreen to VbYellow, VbRed and vbRed

Cheers

Dave
0
 
nelson97Author Commented:
Thanks Brett, that worked.  I will be posting another to do the same thing but using a command button. Thank you!
0
 
DaveCommented:
Thanks for the grade

You could do this without a checkbox to trigger the colour. You could use sheet events such as doubleclick to trigger the colour change in particular cells, I'll give you an example if you wish

Cheers

Dave
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
nelson97Author Commented:
Sure that would be great, brettdj.  I'm basically trying to display inventory by color coding the cells by some trigger event.  I'm using sharing mode, so I'm looking for something that will work well with it.  So I wanted to test out how command buttons would react.

Thanks!
0
 
nelson97Author Commented:
Should I go ahead and create a new thread for my last reply?  Let me know - Thanks
0
 
DaveCommented:
Hi

Right click your sheet tab
View Code
Paste the code below

If you doubleclick a single cell in Columns A to D it will change colour. Double click again and the colour is removed

Cheers

Dave

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'If more than once cell is selected then exit
    If Target.Cells.Count > 1 Then Exit Sub
    Select Case Target.Column
    Case 1
        If Target.Interior.ColorIndex = xlNone Then
              Target.Interior.Color = vbYellow
        Else
            Target.Interior.ColorIndex = xlNone
        End If
    Case 2, 3
        If Target.Interior.ColorIndex = xlNone Then
            Target.Interior.Color = vbGreen
        Else
            Target.Interior.ColorIndex = xlNone
        End If
    Case 4
        If Target.Interior.ColorIndex = xlNone Then
             Target.Interior.Color = vbRed
        Else
            Target.Interior.ColorIndex = xlNone
        End If
    Case Else
        'do not do anything
     End Select
End Sub
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.