Hi Mike-hime,

You can do this with Conditional Formatting and a custom VBA function
(User-Defined Function or UDF). Paste this function into a standard module
in the VBE:

Public Function FileExists(rsPath As String) As Boolean
On Error Resume Next
FileExists = Len(Dir$(rsPath, vbNormal))
On Error GoTo 0
End Function

Now, select the cell (let's say A1) with the filename to check. Select
Format | Conditional Formatting..., select "Formula Is" from the dropdown,
and enter the following formula:

=Not(FileExists(A1))

Now just click the Format... button and select the desired format for
invalid filenames.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Mike-hime wrote:
Greetings fellow VBAers,

I'm writing a workbook that requires the user(s) to input the file
names of other workbooks into a range of cells so that my VBA code
can open and collect data from them. I've written a bit of VBA code
to return an error to the user(s) of my workbook if they have input
the file name of one of these files incorrectly. It looks like this:

Dim USERFILE As Variant

For Each USERFILE In Range("C5:C8")
If Dir("F:\NEWESTIMATE\" & USERFILE & ".xls", vbNormal) = "" Then
MsgBox "' " & USERFILE & ".xls '" & " is not a valid
estimate!", vbOKOnly, "Error!"
End
End If
Next

Is there an excel function that can achieve the same results? I wish
to apply conditional formatting to the cells containing the file
names in such a way that if the user enters a file name, and that
file does not exist, the color of that cell (or some other visual
aid,) will change to alert the user.

Using both of these methods together would be ideal!

Any help would be greatly appreciated. :) TIA

Mike-hime.