VBA to Highlight row if match criteria in another workbook

[gertie193]

Hello,

I'm learning Excel VBA and I would appreciate help. I'm trying to highlight a row of data in one workbook (Table 1) if criteria in column A matches criteria in column A of another workbook.

• I need help referencing the other workbook without naming it explicitly, the Workbooks("AAA_data.xlsx") in this code. I asked how to do this in general before, but I don't know how to make it work here.

• I get an error on the line: Table2.Cells(Imp_Row, Imp_Col) = Application.WorksheetFunction.VLookup(cell, Table2, 1, False) that says Run-Time Error 1004: Unable to get the VLookup property of the WorksheetFunction class. I don't know what that means or what is wrong.

• I would like to see if someone can clean this code up b/c I think I combined two codes I found dealing with this issue (I don't remember b/c I stopped working on it for a while). I NEED to learn VBA and it would help me learn. I'm running into too many situations that call for it now, and I should just know it.

I posted this in another forum a few days ago but received no response. If you view this, please ignore the other code if it's not helpful.

http://stackoverflow.com/questions/40324766/vba-to-highlight-row-if-value-is-found-in-another-workbook

Using Excel 2013 in Windows 7. I hope someone can help. Thank you.

Sub Macro2FINAL()
'http://www.vbaexpress.com/forum/showthread.php?26162-Solved-Highlight-ROW-based-on-cell-value
'http://www.mrexcel.com/forum/excel-questions/827262-visual-basic-applications-vlookup-between-2-workbooks.html
    
    Const TEST_COLUMN As String = "A" '<=== change to suit
    Dim lastRow As Long
    Dim cell As Range
    Dim Imp_Row As Integer
    Dim Imp_Col As Integer
    Dim Table1 As Range
    Dim Table2 As Range
    
    
    Set Table1 = Range("A2", Range("A2").End(xlDown))
    'how do I NOT explicitly name the workbook "AAA_data.xlsx" b/c this workbook will be name differently?
    Set Table2 = Workbooks("AAA_data.xlsx").Sheets("Sheet1").Columns("A:Q")
    
    sSheetName = ActiveSheet.Name
     
    With Worksheets(sSheetName)
        lastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
         
        For Each cell In Table1
        Table2.Cells(Imp_Row, Imp_Col) = Application.WorksheetFunction.VLookup(cell, Table2, 1, False)
        Imp_Row = Imp_Row + 1
            If cell.Value = Cells(Imp_Row, Imp_Col) Then
                cell.EntireRow.Interior.ColorIndex = 39
            Else
                cell.EntireRow.Interior.ColorIndex = xlNone
            End If
    
        Next cell
            
    End With
    
        
End Sub