CODEWITHSUNDEEP
excelexcel-2010vba
udaya726
udaya726

Reputation: 1018

Change column value of the copy range in excel

In an excel file when user selects a value in column 2, I am setting a value in column one based on the selection. I have written that code in the Private Sub Worksheet_Change(ByVal Target As Range) method.

But when user copy and paste multiple lines the value is set only for the first line in the copy range. Anybody have an idea to set the all the values.

Upvotes: 0

Views: 36

Answers (1)

teylyn
teylyn

Reputation: 35990

You can use a "for" loop to go through all cells in the target.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range

If Not Intersect(Target, Range("B:B")) Is Nothing Then
    For Each cel In Target
      cel.Offset(0, -1) = "Some Value"
    Next cel
End If
End Sub

Upvotes: 1

Related Questions