worksheet change

[londoned]

Any help on this? I'll 'scale' it up for my own needs later.
At the moment I have a macro for each cell in the b column of section one
comparing to cells in b column of section two that uses lots of 'If', 'Then'
and 'ElseIf' fired by a worksheet change in section one. Coding to make this
work is huge, inefficient and prone to typo errors.

There are two sections on worksheet.
Section one range is A10:E20
Section two range is A30:E40

Can you explain how to use loops, variables and worksheet change to make the
following happen?
If there is a match between the values in the B column in both sections then
the values from from corresponding rows are are carried down.

Examples:

If the value in B11= value in B34 then A34 = A11, C34 = C11 and E34 = C11
If the value in B15= value in B30 then A30 = A15, C30 = C15 and E34= C15

 

[Martin Fishlock]

Have a look at the following in the sheet code.

Option Explicit

Private Sub Checkline(rCell As Range)

Dim vSRow As Variant
Dim lDRow As Variant

lDRow = rCell.Row
On Error Resume Next
vSRow = "Error"
vSRow = Application.WorksheetFunction.Match(rCell.Value,
Me.Range("B10:B20"), 0)
If IsNumeric(vSRow) Then ' found a match
vSRow = vSRow + 9 ' its an index starting at 1 so add 9 to give 10-20
Cells(lDRow, "A") = Cells(vSRow, "A")
Cells(lDRow, "C") = Cells(vSRow, "C")
Cells(lDRow, "E") = Cells(vSRow, "C") ' not E????
Else ' no mach then clear
Cells(lDRow, "A") = ""
Cells(lDRow, "C") = ""
Cells(lDRow, "E") = ""
End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lDRow As Variant

If Intersect(Target, Me.Range("B10:B20", "B30:B40")) Is Nothing Then
Exit Sub
End If

Application.EnableEvents = False ' disable events
If Target.Row < 30 Then ' need to do all the cells
For lDRow = 30 To 40
Checkline Cells(lDRow, "B")
Next lDRow
Else ' only check the one row
Checkline Target ' use the targer in this case
End If
Application.EnableEvents = True
End Sub