VBA to compare column A in sheet 1 & 2, enter value if matched and highlight values that do not.

[bdbly]

Hello All,

I have 2 worksheets named Year 1 and Year 2 with categories. What would be the VBA to compare column A (categories) in sheet Year 1 & Year 2, enter value of column B/Year 1 in column B/Year 2 if matched, and highlight Year 2/Column A categories that are not in Year 1?

Thanks!

 

[bdbly]

Here is what I'm trying to accomplish:

	A	B			A	B
1	Sheet 2 (Year 2) <tbody> </tbody>			1	Sheet 1 (Year 1)
2				2
3	Description <tbody> </tbody>	Code		3	Description <tbody> </tbody>	Code <tbody> </tbody>
4	A	red <tbody> </tbody>		4	A	red
5	B	blue <tbody> </tbody>		5	B	blue
6	F	green <tbody> </tbody>		6	F	green
7	C			7	G	yellow
8	G	yellow <tbody> </tbody>		8	W	orange
9	W	orange <tbody> </tbody>		9	Y	black
10	X			10
11	Y	black <tbody> </tbody>		11

<tbody>
</tbody>

 

[JLGWhiz]

Try this

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, fn As Range
Set sh1 = Sheets("Year 1")
Set sh2 = Sheets("Year 2")
    For Each c In sh2.Range("A2", sh2.Cells(Rows.Count, 1).End(xlUp))
        Set fn = sh1.Range("A:A").Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                c.Offset(, 1) = fn.Offset(, 1).Value
            Else
                c.Interior.Color = vbYellow
            End If
    Next
End Sub

It only uses one color.

 

[bdbly]

Thanks JLGWhiz! That's what I needed!

 

[JLGWhiz]

Thanks JLGWhiz! That's what I needed!

You're welcome,
Regards, JLG

 

[bdbly]

Would it be possible to compare multiple columns in the 2 worksheets, copy code from year 1 to year 2 when there is a match and highlight column values that are new in Year 2? Thanks again!

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H	I
5								Year 2	Code
6		a
7				b
8					c			0.00
9			d					0.00
10					e			0.00
11					f			0.00
12					g			0.00
13							h	0.00
14					i			0.00
15					j			0.00
16					k			0.00
17						l		0.00
18						m		0.00
19					n			0.00
20					o			0.00
21				p				0.00

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Year 2​

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H	I
5								Year 1	Code
6		a
7				b
8					c			0.00	red
9			d					0.00	blue
10					x			0.00	black
11					y			0.00	purple
12					z			0.00	brown
13								0.00
14								0.00
15					j			0.00	yellow
16								0.00
17						l		0.00	pink
18						m		0.00	other
19					n			0.00	silver
20					o			0.00	gold
21				p				0.00	green

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Year 1​