Sub RunMe() Dim sValue As Range Dim lRow1, lRow2 As Integer lRow2 = Sheets("Sheet2").Range("A1").End(xlDown).Row Sheets("Sheet1").Select lRow1 = Range("A1").End(xlDown).Row For Each cell In Range("A2:A" & lRow1) Set sValue = Sheets("Sheet2").Range("A2:A" & lRow2).Find(what:=cell.Value) If sValue Is Nothing Then GoTo NextCell If cell.Offset(0, 3).Value = sValue.Offset(0, 3).Value Then Sheets("Sheet1").Select Range("H" & cell.Row).Value = "No" With Sheets("Sheet1").Range(Cells(cell.Row, "A"), Cells(cell.Row, "H")).Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent2 .TintAndShade = 0.399975585192419 .PatternTintAndShade = 0 End With Sheets("Sheet2").Select Range("H" & sValue.Row).Value = "No" With Sheets("sheet2").Range(Cells(sValue.Row, "A"), Cells(sValue.Row, "H")).Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent2 .TintAndShade = 0.399975585192419 .PatternTintAndShade = 0 End With Else: Sheets("Sheet1").Select Range("H" & cell.Row).Value = "Yes" With Sheets("Sheet1").Range(Cells(cell.Row, "A"), Cells(cell.Row, "H")).Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent2 .TintAndShade = 0 .PatternTintAndShade = 0 End With Sheets("Sheet2").Select Range("H" & sValue.Row).Value = "Yes" With Sheets("sheet2").Range(Cells(sValue.Row, "A"), Cells(sValue.Row, "H")).Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent2 .TintAndShade = 0 .PatternTintAndShade = 0 End With End If NextCell: Next cell End Sub
Sub RunMe() Dim lRow As Integer Dim rFound As Boolean lRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row Sheets("Sheet2").Select For Each cell In Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row) rFound = False x = 1 Do x = x + 1 If cell & cell.Offset(0, 1) & cell.Offset(0, 2) & cell.Offset(0, 3) & cell.Offset(0, 4) & cell.Offset(0, 5) _ = Sheets("Sheet1").Cells(x, 1) & Sheets("Sheet1").Cells(x, 2) & Sheets("Sheet1").Cells(x, 3) & _ Sheets("Sheet1").Cells(x, 4) & Sheets("Sheet1").Cells(x, 5) & Sheets("Sheet1").Cells(x, 6) Then rFound = True Sheets("Sheet1").Select Sheets("Sheet1").Range(Cells(x, "N"), Cells(x, "R")).Copy Sheets("Sheet2").Select Sheets("Sheet2").Range(Cells(cell.Row, "N"), Cells(cell.Row, "R")).PasteSpecial Application.CutCopyMode = False If cell.Offset(0, 6) <> Sheets("Sheet1").Cells(x, 7) Then cell.Offset(0, 6).Interior.Color = 255 Sheets("Sheet1").Cells(x, 7).Interior.Color = 5287936 End If If cell.Offset(0, 7) <> Sheets("Sheet1").Cells(x, 8) Then cell.Offset(0, 7).Interior.Color = 255 Sheets("Sheet1").Cells(x, 8).Interior.Color = 5287936 End If End If Loop Until x = lRow If rFound = False Then Range(Cells(cell.Row, 1), Cells(cell.Row, 8)).Interior.Color = 255 Next cell End Sub
Sub RunMe() Dim uValue As Range Sheets("Sheet1").Select For Each cell In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row) Set uValue = Sheets("Sheet2").Columns("A:A").Find(cell.Value) If uValue Is Nothing Then cell.Interior.Color = 255 Next cell End Sub
Thank you for your prompt response.
You asked "So sheet1 has the old prices and sheet2 has the updated prices. Then why is sheet1 still relevant?"
It's relavant because if the value in the Column A on sheet 1 matches Sheet 2, Then I need to see if the valve in Column D has changed or stay the same.
We are finding not all products are changing in price. I cant just take the new spreadsheet and upload it into our CRM/Products table, I Have to enter it manually, Thus why i'm trying to compare the data between old and new, So as to make my import across policy a little easier, God Help me if they have all changed..
I will try what you have suggested and let you know, if your equation solves my riddle. Thanking you again. Ryan
I don't believe this can work. Because The supplier add and remove products from month to month. So if I "Put the prices from sheet1 in a different column then the prices from sheet2. " Unless I sit there and manually copy each line, ensuring its Col A data matches, I can't just copy the Col. without first knowing if The data matches. I'll upload part of the document in bow below so u can see what I mean.
Thanking you again for taking time to assist me.