Strikethrough columns A-J with double click

[sdl93]

Hi everyone,

So I'm a bit of a novice when it comes to excel and I'm looking for a bit of advice/guide on achieving the above. I have a huge list of over 2000 tasks that my work is working through. I want to be able to strikethrough the whole row from columns A-J with either a double click or checkbox once the task is complete.

It seems the double-click method may be the easiest to achieve, I tried to figure it out using checkboxes but realised they would need to be manually linked, which just isn't feasible with over 2000 entries. Any help in this matter would be greatly appreciated. Below is an example of how I would like it to look. Is this possible with one double-click or would it need to be each column?

 

[RobP]

Hi ,

maybe this could work for you ? (where I have defined my range as A2:f12 for example - as the area of click)

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    
    If Not Intersect(Target, Me.Range("A2:f12")) Is Nothing Then
        With Sheet1
            Target.EntireRow.Font.Strikethrough = True
        End With
    End If
End Sub

 

[sdl93]

Hi ,

maybe this could work for you ? (where I have defined my range as A2:f12 for example - as the area of click)

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
   
    If Not Intersect(Target, Me.Range("A2:f12")) Is Nothing Then
        With Sheet1
            Target.EntireRow.Font.Strikethrough = True
        End With
    End If
End Sub

Thanks so much! This worked a treat. Is it possible to add in a line that would remove the strikethrough on another double-click?

 

[Joe4]

Welcome to the Board!

Here is my version of the code that will automatically strikethrough columns A-J of any row that you double-click in.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    Dim r As Long
    
'   Get row double-clicked in
    r = Target.Row
    
'   Strike through columns A-J of that row
    Range("A" & r & ":J" & r).Font.Strikethrough = True
    
    Cancel = True
    
End Sub

Note that this code MUST be placed in the proper sheet module!

 

[offthelip]

this will do it for you, put this in the worksheet code module:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    With Range("A:I").Font
        .Strikethrough = True
    End With
End Sub

 

[Joe4]

Hi ,

maybe this could work for you ? (where I have defined my range as A2:f12 for example - as the area of click)

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
   
    If Not Intersect(Target, Me.Range("A2:f12")) Is Nothing Then
        With Sheet1
            Target.EntireRow.Font.Strikethrough = True
        End With
    End If
End Sub

Just note that will strikethrough ALL the columns in the row, not just columns A-J, as was requested.

 

[RobP]

or this if you just wish to operate on the whole sheet. (remember Sheet1 = name of your worksheet, so you might need to change that in both sets of code if yours is called something different.

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    
        With Sheet1
            Target.EntireRow.Font.Strikethrough = True
        End With
    
End Sub

 

[Joe4]

OK, here is a version that will only affect columns A-J if you click in columns A-J and will toggle back-and-forth between strikethrough and not strikethrough:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    Dim r As Long
    Dim rng As Range
    
'   Limit select range to columns A-J
    Set rng = Range(Target, Range("A:J"))
    If rng Is Nothing Then
        Cancel = True
        Exit Sub
    End If
    
'   Get row double-clicked in
    r = Target.Row
    
'   Strike through columns A-J of that row
    Range("A" & r & ":J" & r).Font.Strikethrough = Not (Range("A" & r).Font.Strikethrough)
    Cancel = True
    
End Sub

 

[Joe4]

or this if you just wish to operate on the whole sheet. (remember Sheet1 = name of your worksheet, so you might need to change that in both sets of code if yours is called something different.

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
   
        With Sheet1
            Target.EntireRow.Font.Strikethrough = True
        End With
   
End Sub

That usually isn't necessary to designate the sheet name. By default, "Worksheet" event procedure code is found in the specific worksheet tab of the sheet that you want to run it against.
It is meant to run against that worksheet only, by default. So you usually don't need to qualify sheet names, unless you specifically want to run it against other sheets (which does not appear to be the case here).

 

[Snakehips]

If you don't / can't use vba then you could perhaps use Conditional Formatting?

Use a Custom formula similar to below. And in the Font tab, tick Effects option for Srikethrough

MXLBook1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K
1	Header	Header	Header	Header	Header	Header	Header	Header	Header	Header	Status
2	Stuff	Example	Stuff	Stuff	Stuff	Stuff	Stuff	Stuff	Stuff	Stuff	Done
3	Stuff	Example	Stuff	Stuff	Stuff	Stuff	Stuff	Stuff	Stuff	Stuff
4	Stuff	Example	Stuff	Stuff	Stuff	Stuff	Stuff	Stuff	Stuff	Stuff
5	Stuff	Example	Stuff	Stuff	Stuff	Stuff	Stuff	Stuff	Stuff	Stuff
6	Stuff	Example	Stuff	Stuff	Stuff	Stuff	Stuff	Stuff	Stuff	Stuff	Done
7	Stuff	Example	Stuff	Stuff	Stuff	Stuff	Stuff	Stuff	Stuff	Stuff	Done
8	Stuff	Example	Stuff	Stuff	Stuff	Stuff	Stuff	Stuff	Stuff	Stuff
9	Stuff	Example	Stuff	Stuff	Stuff	Stuff	Stuff	Stuff	Stuff	Stuff
10
11
12
13
14
Destination

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:J2000	Expression	=$K2="Done"	text	NO

EDIT: The CF doesn't render in the above XL2BB but will in the sheet proper.