HI
try these codes
Sub change()
dim x as long, y as long, a as long, b as long
x = Worksheets("Supplier Price List").Cells(Rows.Count, 1).End(xlUp).Row
y = Worksheets("CSV to be Updated").Cells(Rows.Count, 1).End(xlUp).Row
For a = 2 To y
Worksheets("CSV to be Updated").Cells(1, 4) = "=match(A" & a & ",'Supplier Price List'!A2:A" & x & ",0)"
Worksheets("CSV to be Updated").Cells(2, 4) = "=iserror(D1)"
If Worksheets("CSV to be Updated").Cells(2, 4) = False Then
b = 1 + Worksheets("CSV to be Updated").Cells(1, 4)
If Worksheets("Supplier Price List").Cells(b, 2) <> Worksheets("CSV to be Updated").Cells(a, 3) Then
Worksheets("CSV to be Updated").Cells(a, 3) = Worksheets("Supplier Price List").Cells(b, 2)
Worksheets("CSV to be Updated").Cells(a, 3).Interior.ColorIndex = 6
End If
End If
Next a
End Sub
It will replace the price in sheet 2 if it does not match in sheet1 and colors it yellow.
Ravi
Bookmarks