Forum Discussion
Toggle colour of a clicked-on cell
Excel does not really have an event for when a cell is clicked. It has an event that occurs when the selection changes. If you select a cell by clicking on it or using the keyboard to move to it, the event will occur. But if you click on the same cell again, no event occurs because the selection doesn't change.
It might be better to use a double-click.
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the following code into the worksheet module.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Const TheCells = "A2:A5,C2:C5" If Not Intersect(Range(TheCells), Target) Is Nothing Then If Target.Interior.Color = vbRed Then Target.Interior.Color = vbGreen Else Target.Interior.Color = vbRed End If Cancel = True End If End SubChange the value of the constant TheCells to the list of cells you want to toggle.
Switch back to Excel.
Save the workbook as a macro-enabled workbook.
Make sure that you allow macros when you open it.
HansVogelaar
This is great, but is there a way of it cycling through red, green, white, red, green, white. So if it is miss clicked it can be cycled through to get the correct outcome
Yes, you can cycle through three (or more) colors by adding logical tests; for example:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'---- For cycling colors:
Const rngColorCycleCells = "A2:A30,C2:C30"
Const in4DarkGreen As Long = &H6400
Const in4Red As Long = &HFF
Const in4White As Long = &HFFFFFF
'For more color codes, see "Hex triplet" values on pages linked from _
https://en.wikipedia.org/wiki/List_of_colors_by_shade
'---- If the double-clicked cell is within the color cycling area,
' cycle the font color through the three defined colors.
If Not Intersect(Range(rngColorCycleCells), Target) Is Nothing Then
If Target.Font.Color = in4Red Then
Target.Font.Color = in4DarkGreen
ElseIf Target.Font.Color = in4DarkGreen Then
Target.Font.Color = in4White
ElseIf Target.Font.Color = in4White Then
Target.Font.Color = in4Red
Else '...if that cell's font color is any other color...
'[It's your decision: Either start the color cycle with the
'next statement, or make no change by commenting it out or
'removing it.]
Target.Font.Color = in4Red
End If
Cancel = True
End If
End Sub
This is great. Thank you. And can I do the same but with each double click not only does the colour change but the text in the box change? i.e. completed, on schedule, behind schedule etc?
Yes, the text in the cell can be changed at the same time. The simplest change would be to assign an appropriate value in statements such as:
Target.Value = "Completed"in multiple lines immediately after a color is changed. (If you have code in your Worksheet_Change event handler, you can avoid triggering that event by wrapping each of those assignments with Application.EnableEvents = False (before the assignment) and Application.EnableEvents = True (after the assignment) statements.)
Alternatively (and a better design) would be to check for the expected text of the cell and change only the text; as for color, handle that with conditional formatting.
Another alternative (which avoids VBA altogether, and does not relquire double clicks) would be just to use a dropdown list (a feature made available via Data Validation) of permitted values.
