try this it will indicate the results if folder or files dont exist
Option Explicit
Sub My_Newest_Files()
Dim objFSO As Object
Dim objFile As Object
Dim mfile As String
Dim rngFolder As Range
Set objFSO = CreateObject("Scripting.FileSystemObject")
For Each rngFolder In Range("A1", Cells(Rows.Count, "A").End(xlUp))
If Right(rngFolder.Value, 1) <> "\" Then rngFolder.Value = rngFolder.Value & "\"
If objFSO.FolderExists(rngFolder.Value) Then
mfile = Dir(rngFolder & "*" & rngFolder.Offset(, 1).Value & "*.*")
Do While mfile <> ""
If mfile = "" Then
rngFolder.Offset(0, 3).Value = "No File Found"
End If
mfile = Dir
If objFSO.FileExists(rngFolder.Value & mfile) Then
Set objFile = objFSO.GetFile(rngFolder & mfile)
If objFile.DateLastModified > rngFolder.Offset(0, 2).Value Then
rngFolder.Offset(0, 2).Value = objFile.DateLastModified
rngFolder.Offset(0, 3).Value = mfile
End If
End If
Loop
Else: rngFolder.Offset(0, 2).Value = "Folder does not exist"
End If
Next
Set objFSO = Nothing
Set objFile = Nothing
End Sub