Solved

Program to change a cell box to a certain color

Posted on 2004-10-15
6
298 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 50

Accepted Solution

by:
Dave Brett earned 500 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
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!

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
egit plugin on eclipse 8 129
Selenium and Interactive Data Language 3 78
Is online banking safe? 11 127
Java List 4 76
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
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.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

710 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