Macro/VBA Help: Need to highlight cells that do not contain specific texts

[jodeeeh]

Hi everyone. I am super new in doing macros/VBA codes. I just want to ask about this project that I want to develop.
We currently have a macro that automatically generates the names of files in a specific folder. However I wanted to add something like a "cross-checking" power in which those filenames should contain specific texts/words that are available on a list in a separate spreadsheet. And if they do not contain such texts/words, they would be marked red or highlighted.

Hope I was able to explain the situation clearly! Really really need your help on this. Thanks!

 

[gtwo]

Hey there!

First, check the image below to see the two sheets I used in this example, but you can change the code to your references

Sub check_file_names()
    
    Dim x As Integer            'variable used to loop through cells
    Dim y As Integer            'variable used to loop through cells
    Dim file_name As String     'variable to store each file name
    Dim condition As String     'variable to store each condition
    
    'This part will do a loop through each cell in column A, to check your conditions
    
    For x = 1 To Sheets("Imported_files").Range("A5000").End(xlUp).Row     'put the name of your sheet your file names were imported
        
        file_name = Sheets("Imported_files").Range("A" & x).Value
        
        'Now, for each file name, do a loop through each item on your conditions list
        For y = 1 To Sheets("Conditions").Range("A5000").End(xlUp).Row
            
            condition = Sheets("Conditions").Range("A" & y)
            
            If InStr(1, file_name, condition) > 0 Then
                Sheets("Imported_files").Range("A" & x).Interior.Pattern = xlSolid
                Sheets("Imported_files").Range("A" & x).Interior.Color = 255    '255 is the color red
            End If
        
        Next y
  
    Next x
    
    '### PS. I am assuming your files names are in column A, and your conditions are also in column A ###
End Sub

I hope this can help you