check exist of filenames in column, mark red if missing

[PROTMAN]

Greetings,

I'm a little out of practice with my VB, and haven't done (m)any excel
macros, but I have been put to a task:

I have a column of filenames in a spreadsheet. ex:
joe123.mp3
musicface.mp3
triscuit77.mp3

These files are in a directory below the location of the spreadsheet
which will always be: /_Archive/

I want to have the spreadsheet automatically check the column of
filenames for existence in the /_Archive/ directory. If the file does
not exist, or does not match the capitalization, change the filename's
color in the spreadsheet to red.


The purpose of this is to remove the human eyeballing part of the
process in which we often are missing files that we need.

 

[Tom Ogilvy]

Dim rng as Range, rng1 as Range, cell as Range
Dim sStr as String, sPath as String
sPath = ThisWorkbook.Path & "\_Archive\"
set rng = ThisWorkbook.Worksheets("Data").Range("A2")
set rng1 = .rng.Parent.Range(rng,rng.End(xldown))
for each cell in rng1
sStr = dir(sPath & cell.Value)
is len(sStr) = 0
Cell.Font.ColorIndex = 3
elseif sStr <> cell.Text then
Cell.Font.ColorIndex = 3
else
Cell.Font.ColorIndex = xlAutomatic
end if
Next

However, filenames are really case insensitive, so you might want to relax
that constraint.