View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.misc
Otto Moehrbach
 
Posts: n/a
Default COMMAND BUTTONS and CELL COLOR

Not sure what you are doing but I'll give it a shot. You mention Command
Buttons in the subject of your post but say nothing about buttons in the
post. I think you want an easy way for the user to change the color of a
cell. I gather from what you say that the various colors the user might
want for a cell is limited to just 2 or 3. Is this correct?
I would use a Worksheet_SelectionChange event macro. The code would be
written to cycle through the various colors as the user clicks on the cell.
In other words, if the user clicks on the cell, the color of the cell would
change to Color1. If he clicks on the cell again, the color would change to
Color2, and so forth, until he gets the color he wants. Is this something
that you think would work for you? The code would look something like the
following. Note that this is a sheet macro and must be placed in the sheet
module of the sheet in question. To access that module, right-click on the
sheet tab, select View Code, and paste this macro into that module. Click
on the "X" in the top right corner of the module to return to the sheet.
This macro will cycle from no color to yellow to red to blue to no color in
that order. If you have more than these 4 colors, I would change the code
and use a Select Case construct. Post back if that is the case. HTH Otto
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Not Intersect(Target, Range("C3:H19")) Is Nothing Then
If Target.Interior.ColorIndex = xlNone Then
Target.Interior.ColorIndex = 6
Else
If Target.Interior.ColorIndex = 6 Then
Target.Interior.ColorIndex = 3
Else
If Target.Interior.ColorIndex = 3 Then
Target.Interior.ColorIndex = 5
Else
If Target.Interior.ColorIndex = 5 Then _
Target.Interior.ColorIndex = xlNone
End If
End If
End If
End If
Application.EnableEvents = False
Range("C2").Select
Application.EnableEvents = True
End Sub
"hookfault" wrote in message
...
My worksheet is a usable form with cells that are selected by changing
thier
color. If the cell is a certain color, the reader of the form knows which
selection the creator has made. For example there are two imformational
criteria, one set listed along a row and the other in the left column. The
user will select the aplicable info in the top row and left column and
change
the color of the cell where they meet. This tells the reader what both
criteria were met. basily a grid. I want an easy way for the form user to
change the color of the cell without having to format the cell each time