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

[Dazzawm]

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
	K	AD
2	M10200360000001	2906LM
3	M10200360000001	2907RM
4	M10200360000001	2908LMB
5	M10200360000001	2909RMB
6	M10200360000002	2906LM
7	M10200360000002	2906LM
8	M10200360000002	2907RM
9	M10200360000002	2907RM
10	M10200360000002	2908LMB
11	M10200360000002	2910LMB
12	M10200360000002	2910RMB
13	M10200360000002	2909RMB
14	M10200360000003	2906LM
15	M10200360000003	2907RM
16	M10200360000003	2908LMB
17	M10200360000003	2909RMB
18	M10200360000004	2906LM
19	M10200360000004	2907RM
20	M10200360000004	2908LMB
21	M10200360000004	2909RMB
Sheet2

Excel 2010
	K	AD
2	M10200360000001	NYA
3	M10200360000001	NYA
4	M10200360000001	2906LM
5	M10200360000001	2907RM
6	M10200360000001	NYA
7	M10200360000001	NYA
8	M10200360000001	2908LMB
9	M10200360000001	2909RMB
10	M10200360000002	NYA
11	M10200360000002	NYA
12	M10200360000002	2906LM
13	M10200360000002	2907RM
14	M10200360000002	NYA
15	M10200360000002	NYA
16	M10200360000002	2908LMB
17	M10200360000002	2909RMB
18	M10200360000003	NYA
19	M10200360000003	NYA
20	M10200360000003	2906LM
21	M10200360000003	2907RM
22	M10200360000003	NYA
23	M10200360000003	NYA
24	M10200360000003	2908LMB
25	M10200360000003	2909RMB
26	M10200360000004	NYA
27	M10200360000004	NYA
28	M10200360000004	NYA
29	M10200360000004	NYA
30	M10200360000004	NYA
31	M10200360000004	NYA
32	M10200360000004	NYA
33	M10200360000004	NYA
Sheet1

 

[Leith Ross]

Hello Dazzawm,

Where you looking for a formula or VBA macro to do this?

 

[Dazzawm]

A macro please. I will put in my personal macro workbook to use on all different files.

 

[Dazzawm]

I hope my explanation of the requirements is ok!

 

[Dazzawm]

Is anyone else able to help with this please? I think its during the night where Leith is and could do with it for work. Thanks.

 

[Dazzawm]

Can anybody help please!?

 

[Dazzawm]

I guess I best go back to the drawing board on this one and think of another solution!

 

[Dazzawm]

Well i guess thats it on this one.

 

[Worf]

No, it is not.
I am having my standard busy week but will work on this as soon as possible.

 

[Dazzawm]

OK thanks Worf