Option Explicit

Sub CheckFiles()
Dim xlSheet As Worksheet
Dim lngLast As Long
Dim lngCount As Long
Dim strPath As String
    strPath = BrowseForFolder("Select folder to process")
    Set xlSheet = ActiveSheet
    With xlSheet
        lngLast = .Cells(.Rows.Count, "A").End(xlUp).Row
        For lngCount = 2 To lngLast '=1 if no header row)
            If FileExists(strPath & .Cells(lngCount, 1)) Then
                Range("B" & lngCount) = "Present"
                Range("B" & lngCount).Font.Color = RGB(0, 255, 0) 'Green
            Else
                Range("B" & lngCount) = "Not Present"
                Range("B" & lngCount).Font.Color = RGB(255, 0, 0) 'Red
            End If
        Next
    End With
lbl_Exit:
    Exit Sub
End Sub

Private Function FileExists(strFullName As String) As Boolean
'Graham Mayor
'strFullName is the name with path of the file to check
Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    If fso.FileExists(strFullName) Then
        FileExists = True
    Else
        FileExists = False
    End If
lbl_Exit:
    Set fso = Nothing
    Exit Function
End Function

Private Function BrowseForFolder(Optional strTitle As String) As String
'Graham Mayor
'strTitle is the title of the dialog box
Dim fDialog As FileDialog
    On Error GoTo err_Handler
    Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
    With fDialog
        .Title = strTitle
        .AllowMultiSelect = False
        .InitialView = msoFileDialogViewList
        If .Show <> -1 Then GoTo err_Handler:
        BrowseForFolder = fDialog.SelectedItems.Item(1) & Chr(92)
    End With
lbl_Exit:
    Exit Function
err_Handler:
    BrowseForFolder = vbNullString
    Resume lbl_Exit
End Function