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

 

[Dazzawm]

Hi worf and Leith, any chance to look at this yet please?

 

[MARK858]

Daz, I think I am miss-reading your criteria. Can you post a copy of Sheet1 with just the highlighted results you want based on the data in post #1.

Nothing else, just the end result.

 

[Dazzawm]

Thanks for showing an interest Mark. If I understand you correctly using the examples in post 1 you would like the result after the macro would be run? If so here it is.

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

The first set of rows that are coloured are missing 2910LMB & 2910RMB and the second set of rows are missing all the numbers which is why they are highlighted.

 

[MARK858]

OK, I think I can see what you want to achieve now but at the moment I am having a bit of a brain freeze on how to achieve it.

I haven't totally given up but it is one I will have to come back to if/when I see the light.

 

[Worf]

- Here is what I understood from your request: for a given group, if not all codes from Sheet2/AD are at Sheet1/AD, then the entire group has to be highlighted.
- Advanced filter requires that the list have headers on the first row.
- The code writes to Sheet3, Sheet4 and column AF at Sheet2. If you are using these, we can change it.
- Please test on a copy of your workbook.

Sub Sat()
Dim r As Range, ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, _
ws4 As Worksheet, i%, j%, problem As Boolean, vr As Range, k%, m%
Set ws1 = Sheets("sheet1"): Set ws2 = Sheets("sheet2")
Set ws3 = Sheets("sheet3"): Set ws4 = Sheets("sheet4")
ws2.[af1] = ws2.[k1]                                                        ' criteria range
ws3.Cells.ClearContents
ws3.Cells.ClearFormats
ws2.[af2] = ""
ws2.[k:k].AdvancedFilter xlFilterCopy, ws2.[af1:af2], ws3.[a1], True        ' unique values
For j = 2 To ws3.Range("a" & Rows.count).End(xlUp).Row
    problem = 0
    ws2.[af2] = ws3.Cells(j, 1)
    ws3.[b:z].ClearContents
    ws3.[b:z].ClearFormats
    ws2.[k:ad].AdvancedFilter xlFilterCopy, ws2.[af1:af2], ws3.[b1], False  ' to Sheet3
    ws4.Cells.ClearContents
    ws1.[k:ad].AdvancedFilter xlFilterCopy, ws2.[af1:af2], ws4.[b1], False  ' to Sheet4
    For i = 2 To ws3.Range("u" & Rows.count).End(xlUp).Row
        Set r = ws4.[u:u].Find(ws3.Cells(i, "u"), , xlValues)
        If r Is Nothing Then problem = 1
    Next
    If problem Then
        ws1.[k:k].AutoFilter 1, ws3.Cells(i - 1, "b")
        Set vr = Intersect(ws1.UsedRange, ws1.[k:k].SpecialCells(xlCellTypeVisible))
        For k = 2 To vr.Areas.count
            For m = 1 To vr.Areas(k).Rows.count
                vr.Areas(k).Rows(m).EntireRow.Interior.Color = RGB(150, 180, 200)   ' highlight
            Next
        Next
    End If
    ws1.[k:k].AutoFilter
Next
End Sub

 

[Dazzawm]

Thanks worf but I don't really understand what is happening. What us being copied to sheets 3 & 4? Can it not be as simple as I ask and just colour in sheet 1? I have emailed you a couple of samples of what the ws should look like before and after code.

 

[Dazzawm]

After running your code on a small sample it copied to sheets 3 & 4 rows that it shouldn't of and that were correct.

 

[MARK858]

Daz, are you asking the question because the code doesn't do what you want or just because you don't understand how it is working?
I am asking because it gives me the same results as you have posted in post#13.

Can it not be as simple as I ask

Daz, what you asked for is not simple.

 

[Dazzawm]

Both. And I wasn't being rude by saying can it not be as simple as I ask, it was just a question. I tried worfs code on another set of data, laid out the same and as I said in the previous post it was copying to sheets 3 & 4 rows of data it shouldn't have because they were correct. Also incorrect rows were left alone.

 

[MARK858]

Did the original data you posted in post #1 came out with incorrect highlights on Sheet1 for you? if yes, do you have headers on Sheet1 and Sheet2 and do they match on both sheets?

laid out the same and as I said in the previous post it was copying to sheets 3 & 4 rows of data it shouldn't have because they were correct. Also incorrect rows were left alone.

Are you saying that the cells in Sheet1 are being incorrectly highlighted with your new data?

I am not interested what is going into Sheet3 and Sheet4 at the moment because that is not the task you set, I just want to know what you are seeing on Sheet1 (I will worry about Sheet3 and Sheet4 if you post the new data including an expected result).