VBA Excel escaping edit mode not working

[simt33]

Hi all,

I'm a bit of a VBA newb, but I'm trying to write a small function in a spreadsheet that will execute code on double-clicking a cell and then escape straight out of the cell edit mode. I gather that this is to be done using Cancel = True, which I have tried, but this does not stop the cell going into edit mode. Here is the code so far:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 
Dim rng1 As Range
Set rng1 = Range("D3:E198")
 
 
If Not Intersect(Target, rng1) Is Nothing Then
 
    If Target.Value = "No" Then
        Target.Value = "Yes"
        Target.Interior.Color = RGB(0, 255, 0)
 
        Cancel = True
 
    ElseIf Target.Value = "Yes" Then
        Target.Value = "No"
        Target.Interior.Color = RGB(255, 0, 0)
 
        Cancel = True
 
    End If
 
End If
 
 
 
End Sub

Any idea why this isn't working?

Thanks,

Simt33

[Mihail]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 
Dim rng1 As Range
    Set rng1 = Range("D3:E198")
 
    If Intersect(Target, rng1) Is Nothing Then
        Exit Sub
    End If
 
    If Target.Value = "No" Then
        Target.Value = "Yes"
        Target.Interior.Color = RGB(0, 255, 0)
     ElseIf Target.Value = "Yes" Then
        Target.Value = "No"
        Target.Interior.Color = RGB(255, 0, 0)
     End If
 
     Cancel = True
 
End Sub