BeforeDoubleClick

[Rob1972]

I have a spreadsheet in which I want to perform a BeforeDoubleClick command on. I have two ranges which are M3 to M49 (range 1) and M50 to M250 (Range2). In range one when i perform a doubleclick I want the contents of the cell to change from "NOT CHECKED" to "CHECKED" and the cell colour to change from red to green (this command already works), the problem arises with the second range, which I want it to do the same as the first range but in this case I also want it to hide the entire row. I have managed to write the code for these to work independently my difficulty is getting them to work together.

The code I have so far is:

 Private Sub Worksheet_BeforeDoubleclick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("$M$3:$M$49")) Is Nothing Then Exit Sub
    Select Case Target
    Case "NOT CHECKED"
        Target = "CHECKED"
        Target.Interior.ColorIndex = 4
    Case Else
        Target = "NOT CHECKED"
        Target.Interior.ColorIndex = 3
If Intersect(Target, Range("$M$50:$M$250")) Is Nothing Then Exit Sub
    Select Case Target
    Case "NOT CHECKED"
        Target = "CHECKED"
        Target.Interior.ColorIndex = 4
        Target.EntireRow.Hidden = True
    Case Else
        Target = "NOT CHECKED"
        Target.Interior.ColorIndex = 3
    End Select
  End Select
End Sub

Any guidance would be gratefully appreciated.

My thanks in advance

 

[nemmi69]

Can you not do a check on the range being double clicked

IE

If Target, Range("$M$3:$M$49") then
do one task
elseif Target, Range("$M$50:$M$250") then
do another task
endif

 

[Rob1972]

hi, apologies for being slightly dim but not sure how that would look code wise

 

[nemmi69]

'Hope this makes it clearer

Private Sub Worksheet_BeforeDoubleclick(ByVal Target As Range, Cancel As Boolean)
'This will run for range "$M$3:$M$49" if it is not empty it will run the code
'then check range "$M$50:$M$250".
If Intersect(Target, Range("$M$3:$M$49")) Is Not Nothing Then
    Select Case Target
    Case "NOT CHECKED"
        Target = "CHECKED"
        Target.Interior.ColorIndex = 4
    Case Else
        Target = "NOT CHECKED"
        Target.Interior.ColorIndex = 3
    End Select
    If Intersect(Target, Range("$M$50:$M$250")) Is Not Nothing Then
        Select Case Target
        Case "NOT CHECKED"
            Target = "CHECKED"
            Target.Interior.ColorIndex = 4
            Target.EntireRow.Hidden = True
        Case Else
            Target = "NOT CHECKED"
            Target.Interior.ColorIndex = 3
        End Select
    End If
End If
End Sub

 

[RoryA]

FYI, you have to use:

If Not Intersect(Target, Range("$M$3:$M$49")) Is Nothing Then

rather than:

If Intersect(Target, Range("$M$3:$M$49")) Is Not Nothing Then

 

[Rob1972]

Thank you to you both, the code for the first range is working perfectly however the secoond range still doesn't work

 

[RoryA]

What's your current code and what does "doesn't work" mean? Errors / does nothing / does wrong thing?

 

[nemmi69]

Does it have to work independent of the first range?

If so do

Private Sub Worksheet_BeforeDoubleclick(ByVal Target As Range, Cancel As Boolean)
'This will run for range "$M$3:$M$49" if it is not empty it will run the code
'then check range "$M$50:$M$250".
If Not Intersect(Target, Range("$M$3:$M$49")) Is Nothing Then
    Select Case Target
    Case "NOT CHECKED"
        Target = "CHECKED"
        Target.Interior.ColorIndex = 4
    Case Else
        Target = "NOT CHECKED"
        Target.Interior.ColorIndex = 3
    End Select
End If
If Not Intersect(Target, Range("$M$50:$M$250")) Is Nothing Then
     Select Case Target
     Case "NOT CHECKED"
         Target = "CHECKED"
         Target.Interior.ColorIndex = 4
         Target.EntireRow.Hidden = True
     Case Else
         Target = "NOT CHECKED"
         Target.Interior.ColorIndex = 3
     End Select
 End If

End Sub

 

[Rob1972]

Hi nemmi69, adding the 'End if' and the end of the first section did the trick, many thanks