Comparing a reference Row to multiple Rows

  • Hello,


    I need help in creating a VBA macro that would compare a row/template to multiple rows then be highlighted if there are differences.


    This is the template:


    Template Loan Interest Rate Interest Type Terms
    Template1 1 5 Fixed 60



    And the below records should be checked if they are in line with the template above. If not, they should be highlighted:


    Attached the file for reference.

    Hope you can help!

    Loan Interest Rate Interest Type Terms
    1 5 Variable 30
    3 3 Fixed 60
    1 5 Fixed 60
    2 4 Fixed Variable

    Files

    • Sample.xlsx

      (9.05 kB, downloaded 17 times, last: )
  • Try this

    Your sample file with code is attached, click the button.

    Files

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Would it be possible to highlight just the difference and not the whole row?


    Template


    Data:


    TemplateLoanInterest RateInterest TypeTerms
    Template115Fixed60
    LoanInterest RateInterest TypeTerms
    15Variable30


    Thank you!

  • Change the code to this

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • You're welcome

    Hello!


    I'm back here again. Was wondering if it would be possible for the template columns do be dynamic as well?


    I saw in the code that :


    Application.ScreenUpdating = 0

    With Sheet1

    x = .[e2].Resize(, 4)

    y = .[e6].CurrentRegion


    Which limits the checking into 4 columns. Was wondering if it is possible to make it dynamic so that user can add/remove columns from the template.


    thank you!

  • Will the headers in the template always exactly match all the data headers?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Attach another sample workbook showing various templates and the corresponding data

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hi KjBox,


    what I mean is that the count of headers of template and the data would always be the same.


    The template can always be changed but the data will always be dependent on the template in terms of checking values.


    Example 1:



    Template Loan Interest Rate Interest Type Terms
    Template1 1 5 Fixed 60
    Loan Interest Rate Interest Type Terms
    1 5 Variable 30


    Example 2



    Template Loan Interest Rate Interest Type Terms Discount Advance Payment
    Template1 1 5 Fixed 60 20% 0
    Loan Interest Rate Interest Type Terms Discount Advance Payment
    1 5 Variable 30 10% 10000





    Hope this helps :)

  • Try this

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • You're welcome, thanks for the "Like"

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.