On Error Resume Next
<u1:p></u1:p> <u1:p>
</u1:p> Set oWbook = ActiveWorkbook
<u1:p></u1:p> book = ActiveWorkbook.Name
<u1:p></u1:p> <u1:p>
</u1:p> ‘Initialize the values from rows 3-2500 to some formatting
<u1:p></u1:p> Set therange = Sheets(3).Range("A3", Sheets(3).Range("A2500"))
<u1:p></u1:p> therange.EntireRow.Font.ColorIndex = 15 'Gray 15%
<u1:p></u1:p> therange.EntireRow.Font.Bold = False
<u1:p></u1:p> therange.EntireRow.Interior.ColorIndex = xlNone
<u1:p></u1:p> <u1:p>
</u1:p> ActiveWindow.WindowState = xlMinimized
<u1:p></u1:p> <u1:p>
</u1:p> ‘Search for any files with extension “.LST” in the same path of wb1
<u1:p></u1:p> With Application.FileSearch
<u1:p></u1:p> .NewSearch
<u1:p></u1:p> 'Change path to suit
<u1:p></u1:p> .LookIn = ActiveWorkbook.Path
<u1:p></u1:p> .FileType = msoFileTypeAllFiles
<u1:p></u1:p> .Filename = "*.LST"
<u1:p></u1:p>
‘If list files “.LST” are found, count total number of list files found
<u1:p></u1:p> If .Execute > 0 Then 'Workbooks in folder
<u1:p></u1:p> For lCount = 1 To .FoundFiles.Count ' <st1:place u4:st="on">Loop</st1:place> through all.
<u1:p></u1:p> 'Open each list file via loop and Set a Workbook variable to it
<u1:p></u1:p> Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
<u1:p></u1:p> ActiveWindow.WindowState = xlMinimized
<u1:p></u1:p> Next lCount
<u1:p></u1:p> oWbook.Activate
<u1:p></u1:p> On Error Resume Next
‘After opening the list files as workbook, loop through each workbook
<u1:p></u1:p> For Each wBook In Application.Workbooks
‘if workbook is wb1, goes to next workbook
<u1:p></u1:p> If wBook.Name = book Then GoTo hey
<u1:p></u1:p> For Each wSheet In wBook.Worksheets
‘set range in wb1, under sheet3 in column B, row 3-2500
<u1:p></u1:p> oWbook.Activate
<u1:p></u1:p> Set origRange = Sheets(3).Range("b3", Sheets(3).Range("b2500"))
‘loop through each cell in origRange, if the font color is other than Gray, it will go to the next cell in origRange.
<u1:p></u1:p> For Each cell In origRange
<u1:p></u1:p> If Not cell.EntireRow.Font.ColorIndex = 15 Then GoTo yah
<u1:p></u1:p> Set rFound = Nothing
‘else, used that cell to find in the list file opened as workbook
<u1:p></u1:p> Set rFound = wSheet.Cells.Find(What:=cell.Value, After:=wSheet.Cells(1, 1), _
<u1:p></u1:p> LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
<u1:p></u1:p> SearchDirection:=xlNext, MatchCase:=False)
<u1:p></u1:p> ‘if a match is found, in the list file, it will get the current row and column of the cell, set entire row to some formatting, and put “YES” to column A (“VALIDITY”).
If Not rFound Is Nothing Then
<u1:p></u1:p> tarCol = cell.Column
<u1:p></u1:p> tarRow = cell.Row
<u1:p></u1:p> 'cell.Select
<u1:p></u1:p> cell.Font.Bold = True
<u1:p></u1:p> cell.EntireRow.Font.ColorIndex = 1 'Black
<u1:p></u1:p> cell.EntireRow.Interior.ColorIndex = xlNone
<u1:p></u1:p> Cells(tarRow, tarCol - 1).Value = "YES"
‘if no match is found, in the list file, it will get the current row and column of the cell, set entire row to some formatting, and put “NO” to column A (“VALIDITY”).
<u1:p></u1:p> Else
<u1:p></u1:p> tarCol = cell.Column
<u1:p></u1:p> tarRow = cell.Row
<u1:p></u1:p> 'cell.Select
<u1:p></u1:p> cell.Font.Bold = False
<u1:p></u1:p> cell.EntireRow.Font.ColorIndex = 15 'gray
<u1:p></u1:p> cell.EntireRow.Interior.ColorIndex = xlNone
<u1:p></u1:p> Cells(tarRow, tarCol - 1).Value = "NO"
<u1:p></u1:p> End If
<u1:p></u1:p> yah:
<u1:p></u1:p> Next cell
<u1:p></u1:p> Next wSheet
<u1:p></u1:p> wBook.Close SaveChanges = False
<u1:p></u1:p> hey:
<u1:p></u1:p> Next wBook
<u1:p></u1:p> On Error GoTo 0
<u1:p></u1:p>