Reference Cell in Track Changes VBA

[BigTenBoy15]

I am using the below code to track changes in the "V&O Register" tab of a workbook I'm using. I would like the 8th value that it spits out (highlighted in red below) to be the value in column B of the same row that the initial change is being made in. The changes are being tracked in a separate tab called "Changes" so I'm not sure how to go back and reference the cell in a different tab. Please help!!!

Dim vOldValue
Dim User
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strAddress As String
    Dim val
    Dim dtmTime As Date
    Dim Rw As Long


    
    If Intersect(Target, Range("AuditRange")) Is Nothing Then Exit Sub
    
    dtmTime = Now()
    val = Target.Value
    strAddress = Target.Address
    
    Rw = Sheets("Changes").Range("A" & Rows.Count).End(xlUp).Row + 1
    With Sheets("Changes")
        .Cells(Rw, 1).Value = strAddress
        .Cells(Rw, 2).Value = val
        .Cells(Rw, 3).Formula = "=IFERROR(VLOOKUP(" & OnlyNums(strAddress) & ",VOIDReference, 2,FALSE),"""")"
        .Cells(Rw, 4).Value = dtmTime
        .Cells(Rw, 5).Value = vOldValue
        .Cells(Rw, 6).Value = User
        .Cells(Rw, 7).Formula = "=IFERROR(VLOOKUP(""" & User & """, UserNames, 2, FALSE), """")"
[COLOR=#ff0000]        .Cells(Rw, 8).Formula = "='V&O Register'!RC2"[/COLOR]
        
        


    End With
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    vOldValue = Target
    User = Environ("UserName")
    


End Sub

 

[Alan_P]

Hi BigTenBoy15,

This should do the trick...

.Cells(Rw, 8).Value = Sheets("V&O Register").Range("B" & Target.Row).Value

Hope this helps,
Cheers,
Alan.

 

[BigTenBoy15]

YAASSSSS THAT WORKED! Thanks so much, Alan!!

 

[Alan_P]

Happy to help