Bailey,

Highlighting a cell uses the Interior property of a range, so the syntax
could look like the following:

Range("A1").Interior.ColorIndex = 6

In your specific situation, I would probably change your Public Sub to a
Function with a Boolean data type. This way, you can return True for a
successful file move or False for an unsuccessful file move. If False, then
you can color the cell via the RunThroughList procedure. I've provided some
illustrative code below (and removed the MsgBox because that will likely get
rather annoying, if that situation every occurs given the pre-tests for file
and folder). When using Boolean data types, the variable is initialized as
False, so unless you explicitly change it to True, the value will always be
False. I did NOT test the code, but I did change the name of the function
from MoveFiles to IsFileMoved.

I want to point out two subtle notes.
(1) Be careful with "qualifying your ranges." Rather than re-explaining a
concept, see my post (i.e. "Sub QualifyRanges..." procedure and explanation)
in the following thread (the 4th or 5th post):
http://groups.google.com/group/micro...5137ca83744b60

(2) You should probably clear the highlighting prior to running
"RunThroughList" because you may end up with incorrect results if you run
this procedure more than once. (For example, an error on the first run of
"RunThroughList" might be highlighted, but that error might not occur on the
second run (i.e. should not be highlighted but since it was highlighted on
the first run the cell shows an incorrect highlight)). An alternative would
be something like If True Then clear the highlighting (i.e. I think you can
use .Interior.ColorIndex = xlNone) Else highlight as desired.

Best,

Matthew Herbert

Public Function IsFileMoved(ByVal Source As String, _
ByVal Destination As String) As Boolean
Dim oFSO As Object
Dim strDestPath As String
Set oFSO = CreateObject("Scripting.FileSystemObject")
With oFSO

'test if the destination folder exists
If .FolderExists(Destination) Then

'test if the source file exists in destination
strDestPath = .GetFolder(Destination) & "\" & _
.GetFileName(Source)

If Not .FileExists(strDestPath) Then

'test if the source file exists
If .FileExists(Source) Then

'move the file
.MoveFile Source, Destination
IsFileMoved = True
Else
IsFileMoved = False
End If
End If
End If
End With
Set oFSO = Nothing
End Function

Sub RunThroughList()
Dim I As Long
Dim Destination As String
Dim Lastrow As Long
Dim blnMoved As Boolean

Lastrow = Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row

For I = 1 To Lastrow

'here is how to do it in a single statement:
'If Not IsFileMoved(Range("A" & I).Value, "C:\Output\") Then
' color the cell
'End If

'here is how to do it with a variable
blnMoved = IsFileMoved(Range("A" & I).Value, "C:\Output\")
If Not blnMoved Then
Range("A" & I).Interior.ColorIndex = 6
End If
Next I

End Sub


"Bailey" wrote:

Mathew, in your above code in the public sub section - how might I color code
that cell or put a value of missing in the next column - this is so I can
identify all files that are missing or vice versa identify those that are
successful.

I tried passing the value of I into the public sub as a long, but then I did
this
.cells ("A",I).interiorcolor.index=6

but I get a type mismatch?

any suggestiosn