Double click range to strikethrough and clear strikethrough

  • I have worked out how to double click on a range in a worksheet to strike through the value, is there a way i can also double click the same range to un-strikethrough ? I have tried playing about with very basic IF statements but cant seem to figure it out.


    Code
    1. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    2. Range("B2:P2").Select
    3. Selection.Font.Strikethrough = True
    4. End Sub
  • Hello,


    You could test the following

    Code
    1. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    2. If Target.Row <> 2 Then Exit Sub
    3. Range("B2:P2").Font.Strikethrough = Not (Range("B2:P2").Font.Strikethrough)
    4. Cancel = True
    5. End Sub


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • That is brilliant thanks, works exactly as i was hoping. Without taking anymore of your time up could you breakdown line 2 of the code for me please, what is <> 2 actually doing. Its good to know rather than just copy and pasting :)


    Thanks for your time

  • Glad to hear this could help you out :)


    Thanks for your Thanks ...AND for the Like :thumbup:


    Line # 2 translates into : " if User double-clicks on a row different than row 2 exit the event macro " ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Indeed, it can be modified to be dynamic ...


    But you will need to be more precise ... ;) or even better attach a sample file for a tailor-made solution ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Below is a guess for your specific situation


    Code
    1. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    2. ' Dynamic Version : Area within which Event Macro operates = B2:P10 - to be adjusted to your situation
    3. If Intersect(Target, Range("B2:P10")) Is Nothing Then Exit Sub
    4. Dim x As Long: x = Target.Row
    5. Range("B" & x & ":P" & x).Font.Strikethrough = Not (Range("B" & x & ":P" & x).Font.Strikethrough)
    6. Cancel = True
    7. End Sub

    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi Carim, Its so close :)


    I have modified so that the code will work for all the ranges in column B & P, how do i add this code to pick up ranges in B&P : W&AN : AW&BJ. Hope this makes sense, i have been playing about trying to work it out myself but keep getting debug. If i'm honest i can pick up the basics but this is a bit beyond me.


    Code
    1. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    2. If Intersect(Target, Range("B2:P23")) Is Nothing Then Exit Sub
    3. Dim x As Long: x = Target.Row
    4. Range("B" & x & ":P" & x).Font.Strikethrough = Not (Range("B" & x & ":P" & x).Font.Strikethrough)
    5. Cancel = True
    6. End Sub
  • My crystal ball is broken ... ;(


    How do you want me to guess the ranges which are in front of your eyes ... :S


    Why don't you just list them ... !!!

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Below is my second ... and last guess ...



    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi Carim, sorry to hear about your crystal ball being broke :P


    Apologies my explanation even sounds painful to me when i read it back. I have tried the above code and it works perfectly.......and all off guess work :)


    I really do appreciate your help with resolving this. I have been reading up on the intersect / Union to try and gain some understanding, it's slowly making sense but i struggle to put it all together to work, if that makes sense.


    Again thank you for taking your free time out to help