none
Match Case ID with condition on different sheets RRS feed

  • Question

  • Hello All,

    I have a Excel file in which Sheet 1 has "Case ID. & Batch no." I am entering in column A & B respectively.

    And Sheet 2 has the many existing "Case ID. & Batch no." in column D & E"

    I am checking for a macro to highlight the Case ID & Batch no. in cells on Sheet 1 whenever I am entering the Case ID in sheet1 if it matches with the Case ID in sheet 2 but not matching with the the Batch number.

    In the below example the Case ID in A2 & B2 on sheet 1 matches with the Case ID in "D2 & E2" on sheet2. (No action required in this instance)

    However, the Case ID on A3 in sheet1 is matches with the Case ID on D3 in sheet2 but the Batch number is not match.

    So, in this instance I need the macro to highlight only the cells in A3 & B3 in sheet1 when only the batch no. differs but the Case ID matches.

    Can anyone help me on this

    Sheet 1:

    Case ID.

    Batch no.

    Product Code

    ETA

    Recd. Date

    452685

    17

    CCXC

    9/29/2019

    9/26/2019

    250412

    21

    XJKT

    9/28/2019

    9/26/2019

    454975

    44

    E4KSN

    9/29/2019

    9/26/2019

    45485

    59

    DTENT

    9/30/2019

    9/26/2019

    448787

    3

    STEXO

    9/26/2019

    9/26/2019

    Sheet 2:

    Associates

    Order pack

    Service Type

    Case ID.

    Batch no.

    Yes

    Z

    O/N

    452685

    17

    no

    Z

    Urgent

    250412

    22

    Yes

    MM

    O/N

    454975

    44

    Yes

    Z

    O/N

    45485

    60

    no

    Z

    Urgent

    448787

    3

    Yes

    Z

    Urgent

    415454

    43

    Yes

    MM

    O/N

    545878

    087

    no

    Z

    O/N

    525788

    106

    no

    Z

    O/N

    145885

    25

    no

    ZZ

    O/N

    985877

    65

    Yes

    Z

    O/N

    448787

    245

    Yes

    RO

    O/N

    587152

    18

    Regards

    John
    Thursday, December 5, 2019 6:50 PM

Answers

  • I deleted my previous post. With further testing I found errors with the code so I have gone back to just using Conditional Formatting with a Vlookup formula.

    This solution requires a helper column in Sheet2 with the Case Id and Batch No concatenated with an underscore between them. I used column F but you can use any column out to the right but you will need to change the formulas to suit in the conditional formatting. You can hide the column after testing. See the screen shot below with the values and the formula that I used in column F which are displayed in column H.

    Not sure of you expertise here so some guidelines to create the Conditional formatting as follows.

    • Select "Sheet1"
    • Select the range from A2 to last required cell in column B. Note you can use extra rows for future data because the formula ignores blank cells. I used A2:B30.
    • Select "Conditional formatting" (On Home ribbon)
    • Select "New rule"
    • Select "Use a formula to determine which cells to format"
    • Copy the following formula and paste into the field. If necessary, edit Sheet2!$F:$F (which is the Table Array for the Vlookup)
    • Tip to Note when editing a formula in Conditional formatting. Click where the formula is and press F2 to place it in "Edit mode" otherwise if you use the arrow keys then excel thinks you are moving on the worksheet and selecting ranges on the worksheet.

       =AND(NOT(ISBLANK($A2)),NOT(ISBLANK($B2)),ISERROR(VLOOKUP($A2&"_"&$B2,Sheet2!$F:$F,1,FALSE)))

    • Click "Format" button
    • Select the "Fill" tab (top of dialog)
    • Select the required colour and click OK

    I personally think that this is the better solution than using VBA. Even though I am very competent in VBA, to ensure that everything is taken into account with VBA makes it quite complex.

    Screen shot of Sheet2

    Screen shot of Sheet1 after applying conditional format.

    Feel free to get back to me if any problems with this solution.


    Regards, OssieMac

    • Marked as answer by Johnmiller1234 Monday, December 30, 2019 6:26 AM
    Tuesday, December 10, 2019 5:08 AM

All replies

  • I deleted my previous post. With further testing I found errors with the code so I have gone back to just using Conditional Formatting with a Vlookup formula.

    This solution requires a helper column in Sheet2 with the Case Id and Batch No concatenated with an underscore between them. I used column F but you can use any column out to the right but you will need to change the formulas to suit in the conditional formatting. You can hide the column after testing. See the screen shot below with the values and the formula that I used in column F which are displayed in column H.

    Not sure of you expertise here so some guidelines to create the Conditional formatting as follows.

    • Select "Sheet1"
    • Select the range from A2 to last required cell in column B. Note you can use extra rows for future data because the formula ignores blank cells. I used A2:B30.
    • Select "Conditional formatting" (On Home ribbon)
    • Select "New rule"
    • Select "Use a formula to determine which cells to format"
    • Copy the following formula and paste into the field. If necessary, edit Sheet2!$F:$F (which is the Table Array for the Vlookup)
    • Tip to Note when editing a formula in Conditional formatting. Click where the formula is and press F2 to place it in "Edit mode" otherwise if you use the arrow keys then excel thinks you are moving on the worksheet and selecting ranges on the worksheet.

       =AND(NOT(ISBLANK($A2)),NOT(ISBLANK($B2)),ISERROR(VLOOKUP($A2&"_"&$B2,Sheet2!$F:$F,1,FALSE)))

    • Click "Format" button
    • Select the "Fill" tab (top of dialog)
    • Select the required colour and click OK

    I personally think that this is the better solution than using VBA. Even though I am very competent in VBA, to ensure that everything is taken into account with VBA makes it quite complex.

    Screen shot of Sheet2

    Screen shot of Sheet1 after applying conditional format.

    Feel free to get back to me if any problems with this solution.


    Regards, OssieMac

    • Marked as answer by Johnmiller1234 Monday, December 30, 2019 6:26 AM
    Tuesday, December 10, 2019 5:08 AM
  • Hi OssieMac,

    Apologies for the delay in reply, the formula works great and my team started using this.

    Thank you very much

    John

    Monday, December 30, 2019 6:28 AM