Solved Change Cell color with Double click

Microsoft Excel 2010 (academic)
February 21, 2013 at 06:46:33
Specs: Windows 7
I am trying to change cell color with a double click and have it loop from White to green to blue to yellow to purple back to white. I have a working macro but with black in the loop. I try to take it out and then I lose the loop and it gets stuck at black. Below is the macro. How do I get rid of the black without losing the loop??

Sub Auto_Open()
Sheets("Sheet1").OnDoubleClick = "ChangeColor"

End Sub
' Toggle the color for each cell in the current selection.
' Normally this will be a single cell which is double clicked.
' But the macro will toggle the color for any selection
Sub ChangeColor()

mycolor1 = 5296274 'Green
mycolor2 = 15773696 'Blue
mycolor3 = 65535 'Yellow
mycolor4 = 10498160 'Purple

For Each cel In Selection
With cel.Interior
mycolor = .Color
Select Case mycolor
Case Is = mycolor5
.Color = mycolor1
Case Is = mycolor1
.Color = mycolor2
Case Is = mycolor2
.Color = mycolor3
Case Is = mycolor3
.Color = mycolor4
Case Is = mycolor4
.Color = mycolor1
.Pattern = x1None
Case Else
.Color = x1None
End Select

End With

Next cel

End Sub

See More: Change Cell color with Double click

Report •

February 21, 2013 at 13:39:39
✔ Best Answer
I have a very limited knowledge of VBA, but if you change the

Case Else
.Color = x1None
End Select

to read

Case Else
.Color = mycolor1
End Select

It should work like you want.


Report •

February 21, 2013 at 14:02:38
That Worked. Your on a Roll Mike. Thanks

Report •
Related Solutions

Ask Question