Code To Highlight Missing Data

[Dazzawm]

I have 2 worksheets (sheet1 & sheet2). Is there a code that will do the following. On sheet1 in column A there will be data e.g ABC and in column B DEF. On sheet 2 DEF will be in column A and ABC should be in column B. I need the cell or row to be highlighted if it is not and vice versa. Thanks.

 

[james_arackal]

You can try using "eqaul to" conditional formatting for highlighting the required data.

 

[Dazzawm]

Not sure how plus I may want the code to go one step further and insert the missing data.

 

[Dazzawm]

^

 

[Yahya]

try this code

Sub Test()
    Dim LR1 As Long, LR2 As Long, ws1 As Worksheet, ws2 As Worksheet, c As Range, r As Range
    Set ws1 = Sheets("sheet1")
    Set ws2 = Sheets("sheet2")
    LR1 = ws1.Range("A" & Rows.Count).End(xlUp).Row
    LR2 = ws2.Range("A" & Rows.Count).End(xlUp).Row
For Each c In ws1.Range("A2:A" & LR1)
    For Each r In ws2.Range("B2:B" & LR1)
        If c.Value & c.Offset(, 1).Value = r.Value & r.Offset(, -1).Value Then
            c.Resize(1, 2).Interior.ColorIndex = 7
        End If
    Next r
Next c
End Sub

 

[Dazzawm]

Thanks but nothing is getting highlighted on either sheet.

 

[Yahya]

this is the data before run the code
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Header 1</td><td style=";">Header 2</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">ABC</td><td style=";">DEF</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">GHI</td><td style=";">KLM</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">NOT</td><td style=";">TON</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Header 1</td><td style=";">Header 2</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">DEF</td><td style=";">ABC</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">KLM</td><td style=";">GHI</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">NOT</td><td style=";">TON</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br />

and sheet1 after run the code
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Header 1</td><td style=";">Header 2</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="background-color: #FF00FF;;">ABC</td><td style="background-color: #FF00FF;;">DEF</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="background-color: #FF00FF;;">GHI</td><td style="background-color: #FF00FF;;">KLM</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">NOT</td><td style=";">TON</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

 

[Dazzawm]

Your code has highlighted cells when it doesnt have too. I think it may be my fault. ABC is on sheet 1 but if DEF is not there but it is on sheet 2 with ABC next to it then it should be highlighted, which tells me that one of the alternatives is missing. If you look at my example below should explain better

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>HEADER</TD><TD>HEADER</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="BACKGROUND-COLOR: #ffff00">ABC</TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>GHI</TD><TD>JKL</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="BACKGROUND-COLOR: #ffff00">MNO</TD><TD style="BACKGROUND-COLOR: #ffff00"> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>SHEET1</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>HEADER</TD><TD>HEADER</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>DEF</TD><TD>ABC</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD>JKL</TD><TD>GHI</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD>PQR</TD><TD>MNO</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD>SHEET2</TD><TD> </TD></TR></TBODY></TABLE>


ABC is highlighted because the DEF is missing which is next to it on sheet 2, MNO is highlighted because it is missing the PQR which is on sheet 2. GHI and JKL are left alone because both are on both sheets.

Thanks.

 

[Yahya]

try this

Sub Test()
    Dim LR1 As Long, LR2 As Long, ws1 As Worksheet, ws2 As Worksheet, c As Range, r As Range
    Set ws1 = Sheets("sheet1")
    Set ws2 = Sheets("sheet2")
    LR1 = ws1.Range("A" & Rows.Count).End(xlUp).Row
    LR2 = ws2.Range("A" & Rows.Count).End(xlUp).Row
    ws1.Range("A2:B" & LR1).Interior.ColorIndex = 6
For Each c In ws1.Range("A2:A" & LR1)
    For Each r In ws2.Range("B2:B" & LR1)
        If c.Value & c.Offset(, 1).Value = r.Value & r.Offset(, -1).Value Then
            c.Resize(1, 2).Interior.ColorIndex = 0
        End If
    Next r
Next c
End Sub

 

[Dazzawm]

Thanks but that is highlighting everything now!!