CODEWITHSUNDEEP
excelvba
Christoffer Trabjerg
Christoffer Trabjerg

Reputation: 39

Compare cell values with folder names

I only found questions comparing cell values with filenames.

I need to compare a column's cell values with folder names and if they match change their color to red.

Sub cell_value_exists_in_folder_list()

    Dim RangeOfCells As Range
    Dim Cell As Range
    Dim Folder As String

    Dim TotalRow As Long

    TotalRow = Range("A" & Rows.Count).End(xlUp).Row

    Set RangeOfCells = Range("A2:A" & TotalRow)

    For Each Cell In RangeOfCells

        Folder = "C:\" & Cell
        If Cell = Folder Then
            Cell.Font.Color = vbBlack
        Else
            Cell.Font.Color = vbRed
        End If
        
    Next Cell
    
    MsgBox "100%, please check"

End Sub

Upvotes: 0

Views: 196

Answers (1)

Harun24hr
Harun24hr

Reputation: 36880

You need to check the folder is exist or not. You can do that by using DIR() function. Try below.

Sub cell_value_exists_in_folder_list()

    Dim RangeOfCells As Range
    Dim Cell As Range
    Dim Folder As String

    Set RangeOfCells = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)

    For Each Cell In RangeOfCells
        Folder = "C:\" & Cell
        If Dir(Folder, vbDirectory) <> "" Then 'Confirm folder is exist.
            Cell.Font.Color = vbRed
        End If
    Next Cell
    
    MsgBox "100%, please check"

End Sub

Screenshot

Upvotes: 1

Related Questions