How to find and update specific row in worksheet when a cell is updated from another worksheet in the same workbook?

[karmaLee]

Hello, I am working on a project where I have a user form that will send data entries to a worksheet called “Daily Records”. In the user form, there is an option button called “Pending”. When this button is selected, it will also send the data entry to another worksheet (in the same workbook) called “Pending Records”. In the “Pending Records” worksheet, there are 5 columns (date, code, serial no, repair date, status). The date, code, serial no, and status will be filled up by the user form when the “pending” option button is selected.

What I want is to have the “Status” column in the “Pending” worksheet to change from “pending” to “repaired” when the user types in the repair date of the items. In my “daily records” worksheet, I also have a column named “item status”. AT THE SAME TIME, I also want that column to also be updated from “pending” → “repaired” when the user types in the repair date in the “pending records” worksheet. I don’t know where to start because I know that the data entry rows in the “daily records” will not match up with the rows in the “Pending” worksheet since not all items are pending. Since both worksheets contain the items date and code, is there a way to use VBA to find a specific data entry by matching the date and code in both worksheets and update the "Status" column?

 

[tonyyy]

You might consider the following...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws1 As Worksheet, ws2 As Worksheet
Dim LastRow As Long, i As Long

Set ws1 = ThisWorkbook.Sheets("Pending Records")
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
End If
End Sub

The code should be copied/pasted into the Pending Records sheet module.

Happy Holidaze!

Tony

 

[karmaLee]

You might consider the following...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws1 As Worksheet, ws2 As Worksheet
Dim LastRow As Long, i As Long

Set ws1 = ThisWorkbook.Sheets("Pending Records")
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
End If
End Sub

The code should be copied/pasted into the Pending Records sheet module.

Happy Holidaze!

Tony

Hello Tony,
thank you for your help, your code works perfectly! There is just a small inconvenience that I would like to alter. I applied conditional formatting to the "Status" column, where the word "pending" will cause the cell to be filled with red and the word "repaired" will cause the cell to be filled with green. However, when I use this code, the word "repaired" doesn't cause the cell to be filled with green. Is there any way for me to alter or make that conditional formatting happen? Thanks


Happy New Year!!

 

[tonyyy]

How did you apply the conditional formatting? I created two rules using the "Highlight Cells Rules" --> "Equal to"; the first for repaired and the second for pending and the cells changed color appropriately.

 

[karmaLee]

How did you apply the conditional formatting? I created two rules using the "Highlight Cells Rules" --> "Equal to"; the first for repaired and the second for pending and the cells changed color appropriately.

I used "Highlight Cells Rules" --> "Text that contains" --> then red for pending and green for repaired.