Return specific cell in that row using row number

[SandManTFC]

Hi all,

I'm struggling to return what i need from VBA, I'm trying to return the name of the person in column A with the expired date being in column C. I have managed to retrun the row number and the expired date, but no other cell values in that row, screenshot attached.

Private Sub Workbook_Open()


Dim cl As Range
Dim rng As Range
Dim str As String
Dim sht_str As String

Dim sht As Worksheet



sht_str = "Attention! The following training expires within 7 days, or have already expired: " & Chr(10) & Chr(10)
 
sht_strs = "Test"

  For Each sht In Me.Worksheets
        sht_str = sht_str & sht.Name & ":"
        str = ""
    Set rng = sht.Range("A6:D313")
    
    
   
   
    On Error GoTo exit_sub
      For Each rw In rng
        
            If rw.Value = "" Then GoTo Next_rw
         If rw.Value < Date + 8 Then str = str & Chr(10) & "Row -  " & rw.Row & " - " & rw.Value
        
         
Next_rw:
        Next rw
            If str = "" Then str = Chr(10) & "All training is up to date"
        sht_str = sht_str & str & Chr(10) & Chr(10)
    Next sht
MsgBox sht_str, 48, "Expiring Training Dates!"
exit_sub:


End Sub