Forum Discussion
Toggle colour of a clicked-on cell
This should be an easy one, but I have a mental block.....
I'm not brilliant with vb coding, but all I want to do is change a cell's colour (background, there's nothing in it) from Green to Red when it is clicked once. If it is clicked again, change it back to Green. That's all. I've come across the problem that if the macro turns it Red because it is Green, the next line will see it as Red, and turn it back to Green.
I am using Excel on Microsoft 365
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.
11 Replies
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.
In the distant past, when I used VBA a fair bit, I remember enabling repeat selection on a cell I was using as a control (to increment a counter) by letting the event handler move the active cell back to a home cell between clicks.
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 outcomeYes, 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
I'm still a novice at the coding. Similar to the above, I wish to set a range of cells so that it toggles between a green check mark (ie. ✔) and a red X (ie. ✘) on double-click.
It sounds simple but, can't figure out the details of the coding.
Can you help with that?
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" Const vbDarkGreen = &H8000 If Not Intersect(Range(TheCells), Target) Is Nothing Then If Target.Value = ChrW(10008) Then Target.Value = ChrW(10004) Target.Font.Color = vbDarkGreen Else Target.Value = ChrW(10008) Target.Font.Color = vbRed End If Cancel = True End If End SubChange the constant TheCells to suit your needs.
