How to use VBA for multiple rows? Now I have code for 1 row.

[MObius12]

Hello!

my task:
If the value of A6 has changed since the last change, then we display the "name" in cell B6 . If not, leave the cell empty.


Now the VBA works only A6:B6 line.


How to make this formula work for the rest of the rows below (a7:b9999)?
There are many such lines (more than 600)

I have the code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A6")) Is Nothing Then
If Range("A6").Value = Dynamic_data Then
Range("B6").Value = ""
Else
Dynamic_data = Range("A6").Value
Range("B6").Value = "Delta"
End If
End If
End Sub

[Bob Phillips]

Change Dynamic_data to an array, sized 1 To however many rows you want to cater for, and


Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo wsc_exit

Application.EnableEvents = False

If Target.Count > 1 Then Exit Sub

If Not Intersect(Target, Me.Columns(1)) Is Nothing Then

If Target.Row >= 6 Then

If Target.Value = Dynamic_data(Target.Row) Then

Target.Offset(0, 1).Value = vbNullString
Else

Dynamic_data(Target.Row) = Target.Value
Target.Offset(0, 1).Value = "Delta"
End If
End If
End If

wsc_exit:
Application.EnableEvents = True
End Sub

[MObius12]

Thanks for the help.


I'm trying to apply the code, but it doesn't work, most likely I'm making a mistake. :banghead:


Could you show with an example how I should change the code in Dynamic_data because I don't quite understand. For example, the first 15 lines.


My changed:

Private Sub Worksheet_Change(ByVal Target As Range)


On Error GoTo wsc_exit

Application.EnableEvents = False


If Target.Count > 1 Then Exit Sub

If Not Intersect(Target, Me.Columns(1)) Is Nothing Then

If Target.Row >= 6 Then
For go = 6 To 9

If Target.Value = Dynamic_data(Target.Row(go)) Then

Target.Offset(0, 1).Value = vbNullString
Else

Dynamic_data(Target.Row(go)) = Target.Value
Target.Offset(0, 1).Value = "Delta"
End If
End If
End If


wsc_exit:
Application.EnableEvents = True
End Sub

[Bob Phillips]

Here is a workbook.