1

First question on here. I am learning VBA and trying to use it for practical work.

I have loads of zip files, and in each zip there is a specific file that I need to extract and rename. I use Excel to list those files and rename them automatically for me. However the file or zip in general aren’t always there.

How can I have Excel (VBA) change the color of a cell that couldn’t successfully rename a file?

Here is my current code:

Sub RenameCNotes()

    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False

        If .Show = -1 Then
            selectDirectory = .SelectItems(1)
            dFileList = Dir(selectDirectory & Application.PathSeparator & “*”)

            Do Until dFileList = “”
                curRow = 0
                On Error Resume Next
                If curRow > 0 Then
                    Name selectDirectory & Application.PathSeparator & dFileList As _
                    selectDirectory & Application.PathSeparator & Cells(curRow, “D”).Value
                End If

                dFileList = Dir
            Loop
        End If
    End With
End Sub
Alfredo Garcia
  • 11
  • 1
  • Hi! You can analyze Err object with statement such as `If Err.Number <> 0 Then` (https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/err-object) after file operation and do color your cells as you want – Алексей Р May 20 '21 at 18:19

0 Answers0