Create macro to turn a cell yellow, copy and go down one cell

[bearcub]

I have to create some PDF documents from a mail merge. In Excel, I have a list of individual names that copied individually into the word input box (part of a word macro).

What I would like to do in Excel is to copy a cell, highlight it yellow (to show that I've copied the name to word) and then go down one row.

I will be pasting the name from Excel to Word via the Input box in the word macro. I just turned on the macro recorder to demonstrate what I want this macro to do but I would like to attach it to either a worksheet or workbook event:

    Selection.Copy
    Application.CutCopyMode = False
    With Selection.Interior
        .Color = 65535
    End With
    ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

I need to fix this line so that will go down onecell based upon where I have the cursor (not based on A1):

ActiveCell.Offset(1, 0).Range("A1").Select

I was thinking about using the before rightclick or doubleclick events but would this be the best place to put it?

Thank you for your help,

Michael

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window


When you double click on any value in column A that cell will be copied and interior color changed to yellow

My assumption is you always want this script to run only if you double click on a cell in column A if it has some value in the cell

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  1/3/2019  11:28:41 PM  EST
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Cancel = True
Application.CutCopyMode = False
If Target.Value = "" Then Exit Sub
Target.Interior.Color = 65535
Target.Copy
Target.Offset(1).Select
End If
End Sub

 

[bearcub]

Thank you, Column A might be one of the columns but I might have names in other columns as well. How would I modify this code - would I take out the Not Intersect line?

Thank you for your help on this.

Michael

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  1/4/2019  2:40:21 PM  EST
Cancel = True
Application.CutCopyMode = False
If Target.Value = "" Then Exit Sub
Target.Interior.Color = 65535
Target.Copy
Target.Offset(1).Select
End Sub