Find all rows that have a value of a changed row

[treeleaf20]

I have a dependency column (column I) in my spreadsheet and a WBS ID (column A). If I change a date field (column J), I'd like the VBA to find all of the rows that have the changed WBS ID in it. How can I do this with VBA? Some of the dependency columns also have a comma separated list.

For example, say I have the following table:

WBS ID (column A)	Dependency (column I)	Start Date (column J)
1	2	12/16/2019
2	3,1	12/01/2019
3	1	12/19/2019

In this scenario, say I update the row with the WBS ID of 1. In this case, I'd like the system to find this in the Dependency column and have a foreach loop so I can update a value. So in this example, it would find the row for WBS ID 2 and 3.

Thank you in advance.

 

[DanteAmor]

Put the following code in the events of your sheet

Option Explicit
Public dic As Object

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("J:J")) Is Nothing Then
    If Target.Count > 1 Then Exit Sub
    If Target.Row = 1 Then Exit Sub
    Dim ini As Variant
    Set dic = CreateObject("Scripting.Dictionary")
    ini = Cells(Target.Row, "A").Value
    dic(ini) = Empty
    Call Fill_Dic(Cells(Target.Row, "I").Value)
    dic.Remove ini
    MsgBox Join(dic.keys, ",")
  End If
End Sub

Sub Fill_Dic(depen)
  Dim c As Variant, f As Range
  For Each c In Split(depen, ",")
    Set f = Range("A:A").Find(Trim(c), , xlValues, xlWhole)
    If Not f Is Nothing Then
      If Not dic.exists(f.Value) Then
        dic(f.Value) = Empty
        Call Fill_Dic(Cells(f.Row, "I").Value)
      End If
    End If
  Next
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

Go back to your sheet, modify a cell in column J, the code is automatically executed.
The result will be displayed in a message.
Note: You didn't say where you want the result, so I put it in a message.