scubadivingfool
New Member
- Joined
- Jun 17, 2010
- Messages
- 35
Hi All,
Using the below code from someone else. What I am trying to do is to compares a row in Sheet1 with the rows in Sheet2. In shhet1, column A has the SKU, Column B has the Discription and column C has the price. In Sheet2 Column A,B and C are the same with SKU, Description and price but it also has Column D as location and Column E as the type. I only need to compare column A and C in the two sheets. If the row matches with the SKU and price have the row in green, if its a mismatch shows in red and if it's not there either stay as white or grey
Using the below code from someone else. What I am trying to do is to compares a row in Sheet1 with the rows in Sheet2. In shhet1, column A has the SKU, Column B has the Discription and column C has the price. In Sheet2 Column A,B and C are the same with SKU, Description and price but it also has Column D as location and Column E as the type. I only need to compare column A and C in the two sheets. If the row matches with the SKU and price have the row in green, if its a mismatch shows in red and if it's not there either stay as white or grey
VBA Code:
Sub CheckRows()
Dim cl As Range
Dim Ws1 As Worksheet
Dim Ws2 As Worksheet
Dim Vlu As String
Dim Lc As Long
Set Ws1 = Sheets("Sheet1")
Set Ws2 = Sheets("sheet2")
Lc = Ws2.Cells(1, Columns.Count).End(xlToLeft).Column
With CreateObject("scripting.dictionary")
For Each cl In Ws2.Range("A1", Ws2.Range("A" & Rows.Count).End(xlUp))
Vlu = Join(Application.Index(cl.Resize(, Lc).Value, 1, 0), "|")
.Item(Vlu) = Empty
Next cl
For Each cl In Ws1.Range("A1", Ws1.Range("A" & Rows.Count).End(xlUp))
Vlu = Join(Application.Index(cl.Resize(, Lc).Value, 1, 0), "|")
If .exists(Vlu) Then cl.Resize(, Lc).Interior.Color = vbRed
Next cl
End With
End Sub
Last edited by a moderator: