Comparing 2 Sheets in a Workbook

Hello,

Here is my problem. Any pointer in the right direction is greatly appreciated.

I have 2 sheets in the same workbook:

Sheet 1 has 2 columns: "Factor" and "Threshold". There are many unique factors, each having its own numerical threshold.
Sheet 2 has many columns, relevant being "Company","Factor", "Old Value" and "New Value".

There are many companies, each has different set of Factors, and appropriate Old and New Values.

What I need to do is to go through Sheet 2, check every Factor, check the difference between Old and New Value, and highlight the row where the difference is larger than the threshold for that Factor on Sheet1.

Please let me know if any of this is unclear. Any help is greatly appreciated.

Regards,
Lana

Can you attach a sample workbook with sample data and mock-up what you want as your result for that data.

Go Advanced -> Manage Attachments -> Upload

Thank you, kersplash! Please see attached. I highlighted rows in sheet 2 and explained why.

According to your attachment as a beginner starter :

PHP Code: 

Sub Demo1()
     Dim A$
         A = Sheet1.Range("A2", Sheet1.[B1].End(xlDown)).Address(External:=True)
    With Sheet2.[A1].CurrentRegion.Columns("A:D")
        .Interior.ColorIndex = xlNone
         Application.ScreenUpdating = False
        .Range("E2").Formula = "=ABS(C2-D2)>VLOOKUP(B2," & A & ",2,FALSE)"
        .AdvancedFilter xlFilterInPlace, .Range("E1:E2")
        .Rows("2:" & .Rows.Count).Interior.Color = vbRed
         If .Parent.FilterMode Then .Parent.ShowAllData
        .Range("E2").Clear
    End With
         Application.ScreenUpdating = True
End Sub 


Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

No vba.

1) Select A2:Dx, x is a row number that you want to apply.
2) Conditional Formatting Enter Formula :
=ABS($D2-$C2)>=VLOOKUP($B2,Sheet1!$A$1:B$100,2,FALSE)
3) Select cell format.

Yes that's it ! As an Excel worksheet function …

Thank you so much! I learned more from this thread than from reading a book! You guys rock!!!!

Many thanks for the rep's Lana !

You are welcome and thanks for the rep.

Please mark the thread as SOLVED.