Insert double-click code into existing macro

[Jdadad23]

Hello,

I have a report I generate daily to check past work which I have been manually formatting for a few months -- the last thing I do when formatting is use a double click macro so I can double click a cell and it will change the cell color so I know I have already looked at that line. Well I finally put all the formatting into a macro, however I can't seem to add the double click portion of it. Here is the macro I created:

Sub EC_FreightLog()
'
' EC_FreightLog Macro
' Formats the freight log
'
With ActiveSheet
    .AutoFilterMode = False
    With Range("G1", Range("G" & Rows.Count).End(xlUp))
        .AutoFilter 1, "*TK*"
        On Error Resume Next
        .Offset(1).SpecialCells(12).EntireRow.Delete
    End With
    .AutoFilterMode = False
End With
    Cells.Select
    ActiveWorkbook.Worksheets("rep01034866").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("rep01034866").Sort.SortFields.Add Key:=Range( _
        "A2:A105"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("rep01034866").Sort
        .SetRange Range("A1:R105")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Columns("K:M").Select
    Selection.Delete Shift:=xlToLeft
    Columns("N:N").Select
    Selection.Delete Shift:=xlToLeft
    Cells.Select
    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(9), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$M$200").AutoFilter Field:=3, Criteria1:="="
    Range("I4:I139").Select
    Range("I139").Activate
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.AutoFilter
    Columns("A:M").Select
    Columns("A:M").EntireColumn.AutoFit
End Sub

And here is what I would like to add to it:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Interior.ColorIndex = 3
End Sub

I know I can't start a new sub within a sub so I remove those but I still can't seem to get this to run. Any way I have tried to rewrite it with my limited vba knowledge, I either get a compile error or a syntax error. Help is appreciated

*Edit
This part of the code is just a max range I think I would need to fill all the data, but the range I really need is just cells with data in them. So it could be 50 rows or 132 rows, I just don't know how to put that into code, if someone would like to help with that as well, it would be an added bonus.

ActiveSheet.Range("$A$1:$M$200").AutoFilter Field:=3, Criteria1:="="    
Range("I4:I139").Select
Range("I139").Activate

 

[Norie]

Your double click code only appears to set the colour of the cell that's been double clicked, how would it fit into the rest of the code that's doing a whole lot more than that?

 

[JoeMo]

Your formatting code is in a standard module that runs when you launch it. The double-click code runs in the event you double-click a cell on your worksheet and is in a different location than your formatting code. You cannot put event code within your standard module, but you can make an addition to your standard code that will color fill a cell when your formatting is completed. Can you explain how the cell you would double-click is determined?

 

[Jdadad23]

The bulk of my code formats the data by eliminating unnecessary columns, subtotaling like data and coloring the subtotals green. But once that is done, the formatting is done. The double click code then allows me to double click on the subtotal and it will change the color from green to red. That way if I get pulled away from the sheet I am working on, I know exactly where I left off. Basically as I check each subtotal, I click it to mark it off that I have already looked at this line so when I come back to the sheet, I don't check totals I've already looked at.

 

[JoeMo]

The bulk of my code formats the data by eliminating unnecessary columns, subtotaling like data and coloring the subtotals green. But once that is done, the formatting is done. The double click code then allows me to double click on the subtotal and it will change the color from green to red. That way if I get pulled away from the sheet I am working on, I know exactly where I left off.

Why not add a line at the end of your formatting code to change the subtotal cell to red?

 

[Jdadad23]

Why not add a line at the end of your formatting code to change the subtotal cell to red?

I already have the subtotals changing to green. I only change them to red after I have checked the line and verified it is correct on another sheet. So I need that red cell to only run after a specific event has occurred. I just don't want to have to manually enter the macro every single day.

 

[JoeMo]

I already have the subtotals changing to green. I only change them to red after I have checked the line and verified it is correct on another sheet. So I need that red cell to only run after a specific event has occurred. I just don't want to have to manually enter the macro every single day.

Not sure I understand the part in bold - which macro are you referring to, the formatting macro or the event macro?

 

[Jdadad23]

Not sure I understand the part in bold - which macro are you referring to, the formatting macro or the event macro?

The event macro. I run the formatting macro and then I locate a file on my computer with saved macros, copy the event macro, come back to my spreadsheet, right click on the worksheet tab, select "view code", paste the event macro, click Alt-Q and I'm good to go. It's really not that time consuming but I'd like to have the event macro run at the click of a button. I was hoping to add it to the formatting macro since I always use them together, but since I can't do that, I'm hoping I can still run it at the click of a button rather than the copy+paste method I've been using.

 

[JoeMo]

The event macro. I run the formatting macro and then I locate a file on my computer with saved macros, copy the event macro, come back to my spreadsheet, right click on the worksheet tab, select "view code", paste the event macro, click Alt-Q and I'm good to go. It's really not that time consuming but I'd like to have the event macro run at the click of a button. I was hoping to add it to the formatting macro since I always use them together, but since I can't do that, I'm hoping I can still run it at the click of a button rather than the copy+paste method I've been using.

You can replace the event macro with a standard module that you can assign to a button. You would first select the cell you want to change to red then click the button.

Sub MakeRed()
ActiveCell.Interior.Color = vbRed
End Sub

 

[Jdadad23]

You can replace the event macro with a standard module that you can assign to a button. You would first select the cell you want to change to red then click the button.

Sub MakeRed()
ActiveCell.Interior.Color = vbRed
End Sub

And then I could assign a shortcut to it, correct? I think this will work great. Thank you for your help, JoeMo.