Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Program to change a cell box to a certain color

Posted on 2004-10-15
6
Medium Priority
?
309 Views
Last Modified: 2008-03-17
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
0
Comment
Question by:nelson97
  • 3
  • 3
6 Comments
 
LVL 50

Accepted Solution

by:
Dave Brett earned 2000 total points
ID: 12320140
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
 

Author Comment

by:nelson97
ID: 12321741
Thanks Brett, that worked.  I will be posting another to do the same thing but using a command button. Thank you!
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 12325833
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:nelson97
ID: 12325854
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
 

Author Comment

by:nelson97
ID: 12326007
Should I go ahead and create a new thread for my last reply?  Let me know - Thanks
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 12327022
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Introduction to Processes

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question