I need help with setting up a lookup table, or another way to do this

[dsandoz116]

I am currently trying to work with two workbooks. One is where data is gathered where I am wanting to compare row 6 values that are what the values should be and compare this to row 8:23 where data is entered. However, the values can be within a certain tolerance, and I have the tolerances located in another workbook. Below is my code, but it is located in the worksheet object so everytime the worksheet is changed the tolerance table opens. Any suggestions would be helpful.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LResult As String
Dim i As Long
 
    WorkingSheetWBookName = "Workbook 1.xlsm"
    WorkingSheetChange = "Worksheet 1"
    ReferenceSheetName = "Tolerance Lookup"
    ReferenceSheetWBookName = "Reference Sheet working copy.xlsx"
    ReferenceSheetWBookLocation = "D:\Reference Sheet working copy.xlsx"
 
LResult = Mid(Worksheets(WorkingSheetChange).Range("D4").Value, 11, 2)
If LResult = ("PW") Then
    ReferenceSheetCheck
    For i = 8 To 23
        If Cells(i, 3).Value > Range("C6").Value + Workbooks(ReferenceSheetWBookName).Worksheets(ReferenceSheetName).Range("B2").Value Then
           Cells(i, 3).Font.ColorIndex = 1
           Cells(i, 3).Font.Bold = True
           Cells(i, 3).Interior.ColorIndex = 3
        Else
            Cells(i, 3).Font.ColorIndex = 1
            Cells(i, 3).Font.Bold = False
            Cells(i, 3).Interior.ColorIndex = 0
        End If
    Next i
End If
End Sub
 
Private Sub ReferenceSheetCheck()
On Error GoTo OpenIt
Application.Workbooks(ReferenceSheetWBookName).Activate
GoTo Quit
OpenIt:
Workbooks.Open ("D:\Reference Sheet working copy.xlsx")
Quit:
End Sub
 
 

[Rabbit]

Your thread has been moved to the Excel forum.

And what trouble are you having?