Double-clicking on a cell, copy the cell value to another cell in the same row.

Question

This VBA code does what I need, but in only one line:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("C1:H1")) Is Nothing Then
    Cancel = True

    If Application.CountIf(Sheets("Sheet2").Range("B:B"), Target.Value) = 0 Then
        Range("B1").Value = Target.Value
    End If
End If
End Sub

In my Excel worksheet:

A    B   C    D    E   F   G   H
MAN     NN   NNP  JJ  POS
CAN     MOD  NN   VV   JJ  RB  NON
...     ...

up to 300,000 data.

I want to double-click any cell in the C: H range, its value will be copied to B. The above code processes only one row. If I changed C1: H1 to C2: H2 and B1 to B2, it will process the second line.

How to make it work for all lines?

Answer 1

I think the following change will work (not tested, on iPhone):

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    If Not Intersect(Target, Range("C:H")) Is Nothing Then
        Cancel = True

        If Application.CountIf(Sheets("Sheet2").Range("B:B"), Target.Value) = 0 Then
            Cells(Target.Row, 2).Value = Target.Value
        End If
    End If

End Sub