Macro for copy/paste values for only that row where the change is made

[kreso]

I have excel table that I am using (you can see it in attachment), and i made the two codes that you can see lower.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Cancel = True   'Eliminate Edit status due to doubleclick
    Target.Offset(1).EntireRow.Insert
    Target.EntireRow.Copy Target.Offset(1).EntireRow
    On Error Resume Next
    Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents
    On Error GoTo 0
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
   '// Runs when a value in B5 to B100 is changed
    If Not Intersect(Range("B5:B100"), Target) Is Nothing Then
        Range("E5:E100").Value = Range("C5:C100").Value
        Range("F5:F100").Value = Range("D5:100").Value
    End If
End Sub

The thing is that when i manually add some different value (not the one that is auto generate with VLOOKUP function) in some E cell (for example E8), when i pick another choice from drop down list in B column (B9), vba code automatically change my manually entered value to value that is pulled from VLOOKUP.

Is there a way to make vba copy/paste values only for row that is in that moment "active", because now it every time when i make change it copy/paste whole range?


Thanks in advance

[kreso]

Anyone?

[mdmackillop]

Private Sub Worksheet_Change(ByVal Target As Range)
   '// Runs when a value in B5 to B100 is changed
    Dim rw As Long
    rw = Target.Row
    On Error Resume Next
    Application.EnableEvents = False  'Prevent code looping
    If Not Intersect(Range("B5:B100"), Target) Is Nothing Then
        Range("E" & rw).Resize(, 2).Value = Range("C" & rw).Resize(, 2).Value
    End If
Exits:
    Application.EnableEvents = True
End Sub

[kreso]

Thank you so much, it works great.