Answered by:
Match Case ID with condition on different sheets
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 Edited by Johnmiller1234 Thursday, December 5, 2019 6:52 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
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
