CELL COLOR CHANGE ON CLICK

[graemekoz]

Hi everyone

I'm hoping someone may be able to help. I've tried to look at other posts but not sure of the best terminology.

I want to create a checklist in excel so that when I click on (or tap) the specific cell, it changes to one colour and when I click (or tap) again it takes the colour away or puts it as a different colour.

It's be great if a tick or check mark could appear in the cell but i'm not pushing my luck on that one

I imagine it's got to be done through VBA and I've got some coding but once the colour changes it can't be changed back.

Thank you in anticipation

Graeme

 

[kevin9999]

Couple of possibilities offered. The first is a Selection Change code - which does require you to click on another cell first after you've changed the target cell before you select it again to change back. The other is a Double Click code (my preference) which works by double clicking on the cell & doesn't require you to select another cell first. In both examples I've used range B2:B8 to demonstrate (change that to suit) and VBGreen (also change to suit your preference). To use - right click the sheet tab name, select View Code, and put the code in the window that appears on the right of screen.

Selection change code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("B2:B8"), Target) Is Nothing Then
        Application.EnableEvents = False
        With Target
            If .Value = "" Then
                .Font.Name = "WingDings"
                .Value = Chr(252)
                .Interior.Color = vbGreen
            Else
                .Value = ""
                .Interior.Color = xlNone
            End If
        End With
        Application.EnableEvents = True
    End If
End Sub

Double click code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, cancel As Boolean)
    If Not Intersect(Range("B2:B8"), Target) Is Nothing Then
        Application.EnableEvents = False
        cancel = True
        With ActiveCell
            If .Value = "" Then
                .Font.Name = "WingDings"
                .Value = Chr(252)
                .Interior.Color = vbGreen
            Else
                .Value = ""
                .Interior.Color = xlNone
            End If
        End With
        Application.EnableEvents = True
    End If
End Sub

 

[graemekoz]

Couple of possibilities offered. The first is a Selection Change code - which does require you to click on another cell first after you've changed the target cell before you select it again to change back. The other is a Double Click code (my preference) which works by double clicking on the cell & doesn't require you to select another cell first. In both examples I've used range B2:B8 to demonstrate (change that to suit) and VBGreen (also change to suit your preference). To use - right click the sheet tab name, select View Code, and put the code in the window that appears on the right of screen.

Selection change code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("B2:B8"), Target) Is Nothing Then
        Application.EnableEvents = False
        With Target
            If .Value = "" Then
                .Font.Name = "WingDings"
                .Value = Chr(252)
                .Interior.Color = vbGreen
            Else
                .Value = ""
                .Interior.Color = xlNone
            End If
        End With
        Application.EnableEvents = True
    End If
End Sub

Double click code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, cancel As Boolean)
    If Not Intersect(Range("B2:B8"), Target) Is Nothing Then
        Application.EnableEvents = False
        cancel = True
        With ActiveCell
            If .Value = "" Then
                .Font.Name = "WingDings"
                .Value = Chr(252)
                .Interior.Color = vbGreen
            Else
                .Value = ""
                .Interior.Color = xlNone
            End If
        End With
        Application.EnableEvents = True
    End If
End Sub

Thank you so much!!! That's great! Graeme

 

[kevin9999]

Thank you so much!!! That's great! Graeme

Happy to help, and thanks for the feedback