Change cell color on click

[Spydergrl05]

Hi,

I have created a project status report in Excel. Column E is my "progress" column with the rows being the different projects. With the click of the mouse I want to be able to change the cell color AND write the color in the box (want to write the color so if it is printed in black and white, someone can read the color).

All cells will start white and blank (no writting). With 1 click of the mouse it should turn green and "green" should appear in the cell, with a 2nd click of the mouse it should turn yellow and "yellow" should appear in the cell, a 3rd click should turn it orange, a 4th click turn it red, and a 5th click turn it back to white with no writting.

All of the code I have found online changes it from 1 color back to white, but I can't figure out how to allow it to do multiple colors.

Any help would be appreciated!

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 5 Then
    Select Case Target.Value
        Case "": Target.Value = "Green": Target.Interior.ColorIndex = 4
        Case "Green": Target.Value = "Yellow": Target.Interior.ColorIndex = 6
        Case "Yellow": Target.Value = "Orange": Target.Interior.ColorIndex = 46
        Case "Orange": Target.Value = "Red": Target.Interior.ColorIndex = 3
        Case "Red": Target.Value = "": Target.Interior.ColorIndex = xlNone
    End Select
End If
End Sub

 

[chuckchuckit]

And here is a link where went into some experimenting and variations on the code VoG gave you:

http://www.mrexcel.com/forum/showthread.php?t=524640

 

[machopicho]

Try:

Private cnt As Integer
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  
  If Intersect(Target, Column(5)) Is Nothing Then Exit Sub
    cnt = cnt + 1
    Select Case cnt
        Case 1
            Target.Interior.Color = vbGreen
        Case 2
            Target.Interior.Color = vbYellow
        Case 3
            Target.Interior.Color = vbOrange
         Case 
            Target.Interior.Color = vbRed
        Case Else
            Target.Interior.ColorIndex = xlNone
            cnt = 0
    End Select
    
    Cancel = True
End Sub