VBA Code to match and colour cells data

[Eurekaonide]

Hi All

I'm looking for some code that will enable me to loop through until the end of the data range as not sure how long it will be,
but that looks in column A (cell A2) at a Unique ref and then matches this to whatever the cell is wthin Sheet 2 (will alsoways be Column A in Sheet 2 but not necessarily the same row as Sheet 1).
and if an exact match is found colour the cell in Sheet 1 C2 Yellow. I then need it to do like a VLOOKUP using the reference in A2 sheet 1 finding it in Sheet 2 but checking if the contents in column B matches and colouring the cell in Sheet 1 if it does - and so on to column I.
Ive tried using an If and VLOOKUP formula in a conditional formula which seems to work except for the text as some is numbers and some is text within these cells.
Any Ideas?
It kind of looks like this;

Sheet 1

A B C D E ....
1234 First line of text 0 10/10/2019 10/10/2019
2345 second line of text 2 01/02/2019 02/02/2019

Sheet 2
5678 third line of text 9 01/01/2018 01/01/2018
1234 First line of text 0 10/10/2019 11/10/2019
2345 second line of text 1 01/02/2019 02/02/2019

and so on ....

 

[DanteAmor]

Try this

Sub match_and_colour_cells()
    Dim sh1 As Worksheet, sh2 As Worksheet
    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Sheet2")
    For i = 2 To sh1.Range("A" & Rows.Count).End(xlUp).Row
        Set f = sh2.Columns("B").Find(sh1.Cells(i, "A").Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not f Is Nothing Then
            sh1.Cells(i, "C").Interior.ColorIndex = 6
        End If
    Next
End Sub

 

[Eurekaonide]

Hi Thank you for this however it doesn't seem to do anything.
what I the For I = 2 doing? not sure what 2 is ?
May thanks for your help

 

[DanteAmor]

Hi Thank you for this however it doesn't seem to do anything.
what I the For I = 2 doing? not sure what 2 is ?
May thanks for your help

What it does is, start reading the data on sheet1, from row 2 of column A, take the data, look it up on sheet2, in column B, if it finds it, paint cell C on sheet1.

[COLOR=#333333]I then need it to do like a VLOOKUP using the reference in A2 sheet 1 finding it in Sheet 2 but checking if the contents in column B[/COLOR]

I guess that's how your data is in the 2 sheets:


<table style="font-family:Arial; font-size:12pt; border-style: groove ;border-color:#0000FF;background-color:#fffcf9; color:#000000; "><tr><td ><b>Sheet1</b></td></tr></table> <table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:37.07px;" /><col style="width:119.76px;" /><col style="width:13.31px;" /><col style="width:71.29px;" /><col style="width:71.29px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">1234</td><td >First line of text</td><td style="text-align:right; ">0</td><td style="text-align:right; ">10/10/2019</td><td style="text-align:right; ">10/10/2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">2345</td><td > second line of text</td><td style="text-align:right; ">2</td><td style="text-align:right; ">01/02/2019</td><td style="text-align:right; ">02/02/2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table>

<table style="font-family:Arial; font-size:12pt; border-style: groove ;border-color:#0000FF;background-color:#fffcf9; color:#000000; "><tr><td ><b>Sheet2</b></td></tr></table><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:37.07px;" /><col style="width:37.07px;" /><col style="width:119.76px;" /><col style="width:13.31px;" /><col style="width:71.29px;" /><col style="width:71.29px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">5678</td><td >third line of text</td><td style="text-align:right; ">9</td><td style="text-align:right; ">01/01/2018</td><td style="text-align:right; ">01/01/2018</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">1234</td><td > First line of text</td><td style="text-align:right; ">0</td><td style="text-align:right; ">10/10/2019</td><td style="text-align:right; ">11/10/2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">2345</td><td >second line of text </td><td style="text-align:right; ">1</td><td style="text-align:right; ">01/02/2019</td><td style="text-align:right; ">02/02/2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table>

 

[Eurekaonide]

Thank you I have adjusted slightly as the numbers you have in column B Sheet 2 are actually in Column A.
How would I then get it to check the rest of the row?
So A2 matches to something in column A Sheet 2 but then it needs to check if column B, C, D, E (thru to I) also Match from one sheet to the other, then down to the next row.

 

[DanteAmor]

Thank you I have adjusted slightly as the numbers you have in column B Sheet 2 are actually in Column A.
How would I then get it to check the rest of the row?
So A2 matches to something in column A Sheet 2 but then it needs to check if column B, C, D, E (thru to I) also Match from one sheet to the other, then down to the next row.

Ok, then the macro looks for A2 in column A of sheet2
Try this

Sub match_and_colour_cells()
    Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, f As Range
    
    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Sheet2")
    
    For i = 2 To sh1.Range("A" & Rows.Count).End(xlUp).Row
        Set f = sh2.Columns("A").Find(sh1.Cells(i, "A").Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not f Is Nothing Then
            For j = Columns("B").Column To Columns("I").Column
                If sh1.Cells(i, j).Value = sh2.Cells(f.Row, j).Value Then
                    sh1.Cells(i, j).Interior.ColorIndex = 6
                End If
            Next
        End If
    Next
End Sub