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!
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,300
Messages
5,836,489
Members
430,435
Latest member
Benforest1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top