Forum Discussion

daba1955's avatar
daba1955
Copper Contributor
Jan 21, 2022
Solved

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 wh...
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Jan 21, 2022

    daba1955 

    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 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.

HansVogelaar's avatar
HansVogelaar
MVP
Jan 21, 2022

daba1955 

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 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.

Blake_Beaudin's avatar
Blake_Beaudin
Copper Contributor
Nov 24, 2022

HansVogelaar 

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?

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 24, 2022

    Blake_Beaudin 

    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.

    • Blake_Beaudin's avatar
      Blake_Beaudin
      Copper Contributor
      Nov 24, 2022
      That's awesome. Exactly what I needed. Thank you very much. I appreciate your help.
      Very simple...I just couldn't figure out the colour changing part.

Resources