Jan 21 2022 03:04 AM
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
Jan 21 2022 04:07 AM
SolutionExcel 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 Sub
Change 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.
Jan 21 2022 06:11 AM
@Hans Vogelaar
That's great Hans, thank-you !
I suppose the double-click would be better anyway, since it would be less easy to mistakenly flip a cell colour. And I like the way you've side-stepped the catch 22 situation I would have gotten into.
I'll try it out later when I'm at home ....
Nov 24 2022 06:49 AM
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?
Nov 24 2022 07:13 AM
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 Sub
Change the constant TheCells to suit your needs.
Nov 24 2022 07:50 AM
Jan 21 2022 04:07 AM
SolutionExcel 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 Sub
Change 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.