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.

HartJo's avatar
HartJo
Copper Contributor
Jan 04, 2026

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?

SnowMan55's avatar
SnowMan55
Bronze Contributor
Jan 06, 2026

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.

Resources