verify if range of files exist in folder

[Gen Otmin]

Hi,
I have found a code by JoeMo from back at 2012 that I have used.
Now I need it in another place and here I have empty cells in the range from where I get the names to check if files exists.
This results in that the empty cells get marked green as the filenames found in the folder, and I can't figure out how to solve this.
I don't want the empty cells to be marked green as the found files.
Anyone who can help me with this?

Sub LookForFiles()
Dim pStr As String, myFile As String, rng As Range, lrw As Long
Dim fList As Variant

pStr = ActiveWorkbook.Path & "\"

'pStr = "The Path to Your Folder Here" 'Path to folder including final \
lrw = Range("F" & Rows.Count).End(xlUp).Row
Set rng = Range("F2", "F" & lrw)
fList = rng.Value
For i = LBound(fList, 1) To UBound(fList, 1)
    myFile = Dir(pStr & fList(i, 1)) '& "*")
    If Not myFile = vbNullString Then
        rng.Cells(i).Interior.Color = vbGreen
    End If
Next i
End Sub

 

[James006]

Hi,

Not sure to fully understand ...
Following instruction is changing cell interior

rng.Cells(i).Interior.Color = vbGreen

You can always modify it ...
But you do not explain what your final objective is ...

 

[Gen Otmin]

Sorry to be vague in my question. As seen in the picture below the empty cells are marked green and I can't get it not be like that.

I have solved it with conditional formating, the problem is that when someone add a row in the middle of the table all rows below stops working.

Hence why I try this vba code that I have used in a couple of other places.

 

[James006]

You could test following

If rng.Cells(i).Value <> "" Then rng.Cells(i).Interior.Color = vbGreen

 

[Gen Otmin]

You could test following

If rng.Cells(i).Value <> "" Then rng.Cells(i).Interior.Color = vbGreen

Thank you, I have tried way to complicated things to solve this. Coding is not my strong point.

 

[James006]

You are welcome.

Glad you could fix your problem