Change cell color on click

Spydergrl05

New Member
Joined
Nov 28, 2005
Messages
23
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!
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try this: right click the sheet tab, select View Code and paste in

Code:
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
 

machopicho

Well-known Member
Joined
Feb 28, 2010
Messages
606
Try:
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,325
Messages
5,528,023
Members
409,799
Latest member
mlewan_ca

This Week's Hot Topics

Top