Excel: Code To Highlight What Is In Column AD On Sheet 2 But Not In Column AD on Sheet 1 With K As The Delimiter


I have hidden a lot of columns for clarity. The code first needs to concentrate on column K. It needs to look at the number on sheet 2 then the corresponding number on sheet 1. It then needs to look at column AD. Any numbers that are in AD on sheet 2 but not in column AD on sheet 1 then the entire row on sheet 1 needs to be highlighted please. For clarity I have highlighted the 4 different examples a different colour. The data in yellow as you can see the 4 numbers on sheet 2 are listed ok on sheet 1 (next to the corresponding number in K M10200360000001) With the blue data you can see there is a difference, 2910LMB & 2910RMB (I have left clear) are on sheet 2 but when you look at the data in blue on sheet 1 they are not there so all those rows would need to be highlighted (obviously they would all be clear). The data in green is fine as they match on sheet 1. The data in red all the numbers are missing on sheet 1 as they say NYA in all rows so those rows would need to be highlighted. Its one of those ones where it may be harder to explain than it is to find a solution! Excel 2010

KAD
2M102003600000012906LM
3M102003600000012907RM
4M102003600000012908LMB
5M102003600000012909RMB
6M102003600000022906LM
7M102003600000022906LM
8M102003600000022907RM
9M102003600000022907RM
10M102003600000022908LMB
11M102003600000022910LMB
12M102003600000022910RMB
13M102003600000022909RMB
14M102003600000032906LM
15M102003600000032907RM
16M102003600000032908LMB
17M102003600000032909RMB
18M102003600000042906LM
19M102003600000042907RM
20M102003600000042908LMB
21M102003600000042909RMB

Sheet2



Excel 2010
KAD
2M10200360000001NYA
3M10200360000001NYA
4M102003600000012906LM
5M102003600000012907RM
6M10200360000001NYA
7M10200360000001NYA
8M102003600000012908LMB
9M102003600000012909RMB
10M10200360000002NYA
11M10200360000002NYA
12M102003600000022906LM
13M102003600000022907RM
14M10200360000002NYA
15M10200360000002NYA
16M102003600000022908LMB
17M102003600000022909RMB
18M10200360000003NYA
19M10200360000003NYA
20M102003600000032906LM
21M102003600000032907RM
22M10200360000003NYA
23M10200360000003NYA
24M102003600000032908LMB
25M102003600000032909RMB
26M10200360000004NYA
27M10200360000004NYA
28M10200360000004NYA
29M10200360000004NYA
30M10200360000004NYA
31M10200360000004NYA
32M10200360000004NYA
33M10200360000004NYA

Sheet1




This question generated 12 answers. To proceed to the answers, click here.

This thread is current as of February 15, 2017.


For more resources for Microsoft Excel