Linking 2 worksheets in the same workbook without messing up the data entries

[karmaLee]

Hello, I have an excel workbook with multiple worksheets. I have 1 worksheet called “Daily Records” and another called “Pending”. Both worksheets have columns that are filled up by a userform. Both worksheets have a column named “Status”. In the “Pending” worksheet, I also have a column named “Date”. The cells in the “Status” column can change from “pending” → “repaired” when I type in the date in the “Date” column.

What I would like is for the “Status” column in the “Daily Records” to also be updated when the cell value in the “Status” column in the “Pending” worksheet changes from “pending” → “repaired”. I did try to link the cells from both “Status” columns in both worksheets but the data gets messed up since not all data entries in the “Daily Records” worksheets are pending.

To make it clearer and easier to understand, here is an example:
I have a userform that I will use to send data to the “Daily Records” worksheet. If the status of the data entry is pending, I will select an option button in the userform called “pending” and the userform will then send this data entry to the “Pending” worksheet. Let’s say data entry #1 is pending. The data entry will also be sent to the “Pending” worksheet. But subsequently, data entry #2-10 is NOT PENDING, so these data entries do not get sent to the “Pending worksheets”. Data entry #11 has a pending status so it will also be sent to the “Pending: worksheet. Now the “Pending” worksheet has 2 rows of data entries while the “Daily Records” worksheet has 11 rows. If i key in the date for row #1 in “Pending”, row #1 in “Daily Records” will be updated. However, if i key in the date again for row #2 in “Pending”, the data in the “Daily records” worksheet will be messed up. Is there a solution I can try?


Here is my code for reference:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim pendinglist As Worksheet
Set pendinglist = ThisWorkbook.Sheets("Pending")
pendinglist.Range("D2:D1048576").NumberFormat = "dd/mm/yyyy"

Dim ws1 As Worksheet, ws2 As Worksheet
Dim LastRow As Long, i As Long

Set ws1 = ThisWorkbook.Sheets("Pending")
Set ws2 = ThisWorkbook.Sheets("Daily Records")

If Not Intersect(Columns(4), Target) Is Nothing Then
If IsDate(Target) Then
Target.Offset(0, 1).Value = "Repaired"

LastRow = ws2.Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To LastRow
If ws1.Cells(Target.Row, 1) & ws1.Cells(Target.Row, 2) = _
ws2.Cells(i, 1) & ws2.Cells(i, 2) Then
ws2.Cells(i, 5) = "Repaired"
Exit For
End If
Next i
End If


Sheet3.Cells.Range("J2:J1048576").Value = Sheet7.Cells.Range("E2:E1048576").Value

End If

End Sub