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
Solved

Program to change a cell box to a certain color

Posted on 2004-10-15
6
296 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
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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
topping1 challenge 7 110
Apps blocked by Java 9 88
youtube blocking politics 4 55
Back-end Database connect string being set someplace where I can't find it. 10 25
This is an explanation of a simple data model to help parse a JSON feed
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

856 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