Change cell coulor with a single click

L

Legacy 237453

Guest
Hi

Can somebody tell me how i can change the coulor of a cell when somebody clicks the cell using VBA?
I need the cells to turn green.

If you need more info please let me know. :)

Many Thanks
Craig!
 

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
Right click the sheet tab, select View Code and paste in

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.Interior.ColorIndex = 4
End Sub
 
Upvote 0
To avoid users ending up with a complete green sheet, better use the before double click event, and make it reversible.

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Const vGreen As Variant = 43
Select Case Target.Interior.ColorIndex
    Case vGreen
        Target.Interior.ColorIndex = xlColorIndexNone
    Case Else
        Target.Interior.ColorIndex = vGreen
End Select
End Sub
 
Upvote 0
Hi

Yes i just realised this when i started turning the whole workbook green haha

what if i want this...
1 click green
2 click orange
3 click red
4 click back to no coulor or white
then on a loop?

Thansk so much
Craig
 
Upvote 0
Try

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Interior.ColorIndex
    Case xlNone: Target.Interior.ColorIndex = 4
    Case 4: Target.Interior.ColorIndex = 45
    Case 45: Target.Interior.ColorIndex = 3
    Case 3: Target.Interior.ColorIndex = xlNone
End Select
End Sub
 
Upvote 0
Try

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Interior.ColorIndex
    Case xlNone: Target.Interior.ColorIndex = 4
    Case 4: Target.Interior.ColorIndex = 45
    Case 45: Target.Interior.ColorIndex = 3
    Case 3: Target.Interior.ColorIndex = xlNone
End Select
End Sub


Hi

No this does not work :( i just changes the green cells to orange but thats it.

Cheers
Craig
 
Upvote 0
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Const vGreen As Variant = 43
Const vOrange As Variant = 44
Const vRed As Variant = 3

Select Case Target.Interior.ColorIndex
    Case xlColorIndexNone
        Target.Interior.ColorIndex = vGreen
    Case vGreen
        Target.Interior.ColorIndex = vOrange
    Case vOrange
        Target.Interior.ColorIndex = vRed
    Case vRed
        Target.Interior.ColorIndex = xlColorIndexNone
End Select
End Sub
 
Upvote 0
Hi

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)Select Case Target.Interior.ColorIndex    Case xlNone: Target.Interior.ColorIndex = 4    Case 4: Target.Interior.ColorIndex = 45    Case 45: Target.Interior.ColorIndex = 3    Case 3: Target.Interior.ColorIndex = xlNoneEnd SelectEnd Sub</PRE>
This worked fine but the problem is that i need to switch to another cell then go back to the original cell for the coulor to change. Is there a way to get round this?

Cheers
 
Last edited by a moderator:
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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
Back
Top