5

I am trying to create a sheet where our employees can click on a cell to highlight it notating they are working of the task, and then click it again when they are finished with it, and click it a 3rd time if they need to clear the highlight. So far I have come up with the below, which works except that I have to click another cell and come back to the same one again or it will try to edit the cell. I Just want 1 click color change, another click same cell color change 2, another click same cell color change 3. Is there any way to do this?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)    
  'If the target cell is clear
     If Target.Interior.ColorIndex = xlNone Then

        'Then change the background to the specified color
        Target.Interior.ColorIndex = 6

        'But if the target cell is already the specified color
        ElseIf Target.Interior.ColorIndex = 6 Then

        'Then change the background to the specified color
        Target.Interior.ColorIndex = 3

        'But if the target cell is already the specified color
        ElseIf Target.Interior.ColorIndex = 3 Then

        'Then clear the background color
        Target.Interior.ColorIndex = xlNone

    End If    
End Sub
Vland
  • 4,151
  • 2
  • 32
  • 43
user3159079
  • 51
  • 1
  • 1
  • 2

1 Answers1

5

Add a BeforeDoubleClick event with this code in the same sheet:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Cancel = True
    Worksheet_SelectionChange Target

End Sub
tigeravatar
  • 26,199
  • 5
  • 30
  • 38