Color change in single cell with one click

Microsoft Excel 2003 (full product)
December 16, 2009 at 09:51:27
Specs: Windows XP Pro sp2, 512 MB
In excell, I am trying to create a spreadsheet to be continually updated. My first colum is names then in the subsequent columns, I want to place a symbol such as a circle that when I click on that circle it will change the circle to a specific color that will need to be determined by each column. This is kind of going to be used as a check mark system to keep track of completed tasks. I am also looking to do this the simplist way possible. Thank you for helping!!

See More: Color change in single cell with one click

Report •

December 16, 2009 at 20:19:02
Try this...

Paste this code into the VBA editor (Alt-F11)

Right Click each shape and chose Assign Macro.

Locate the name ColorMyShape and chose it.

As written, it will fill the clicked shape with the Fill color that matches the column number that the shape is placed in.

Hope this helps!

Sub ColorMyShape()
'Get name of Clicked Shape
  myShp = ActiveSheet.Shapes(Application.Caller).Name
'Feel free to delete this line
   MsgBox myShp
'Get column of Clicked Shape
  myShpCol = ActiveSheet.Shapes(myShp).TopLeftCell.Column
'Feel free to delete this line
   MsgBox myShpCol
'There are probably lines in this section that can be deleted...
    ActiveSheet.Shapes(myShp).Fill.Visible = msoTrue
'Set fill color to column number
    ActiveSheet.Shapes(myShp).Fill.ForeColor.SchemeColor = myShpCol
    ActiveSheet.Shapes(myShp).Fill.Transparency = 0#
    ActiveSheet.Shapes(myShp).Line.Weight = 0.75
    ActiveSheet.Shapes(myShp).Line.DashStyle = msoLineSolid
    ActiveSheet.Shapes(myShp).Line.Style = msoLineSingle
    ActiveSheet.Shapes(myShp).Line.Transparency = 0#
    ActiveSheet.Shapes(myShp).Line.Visible = msoTrue
'Set line color to column number
    ActiveSheet.Shapes(myShp).Line.ForeColor.SchemeColor = myShpCol
    ActiveSheet.Shapes(myShp).Line.BackColor.RGB = RGB(255, 255, 255)
End Sub

Report •
Related Solutions

Ask Question