Solved

Program to change a cell box to a certain color

Posted on 2004-10-15
6
293 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 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:nelson97
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

I know it’s not a new topic to discuss and it has lots of online contents already available over the net. But Then I thought it would be useful to this site’s visitors and can have online repository on vim most commonly used commands. This post h…
Here we come across an interesting topic of coding guidelines while designing automation test scripts. The scope of this article will not be limited to QTP but to an overall extent of using VB Scripting for automation projects. Introduction Now…
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now