Compare rows in two sheets

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

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:

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

VincentNL99

New Member
Joined
Sep 19, 2021
Messages
32
Office Version
  1. 365
Platform
  1. Windows
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")
   RowCount = Ws1.Cells(Ws1.Rows.Count, "A").End(xlUp).Row
   RowCount2 = Ws1.Cells(Ws1.Rows.Count, "A").End(xlUp).Row
   With CreateObject("scripting.dictionary")
      For Lc = 1 To RowCount2
         Vlu = Ws2.Range("A" & Lc) & Ws2.Range("C" & Lc)
         .Item(Vlu) = Empty
      Next Lc
    
      For Lc = 1 To RowCount
         Vlu = Ws1.Range("A" & Lc) & Ws1.Range("C" & Lc)
         If .exists(Vlu) Then Cells(Lc, 1).EntireRow.Interior.Color = vbRed
      Next Lc
   End With
End Sub
 

scubadivingfool

New Member
Joined
Jun 17, 2010
Messages
35
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")
   RowCount = Ws1.Cells(Ws1.Rows.Count, "A").End(xlUp).Row
   RowCount2 = Ws1.Cells(Ws1.Rows.Count, "A").End(xlUp).Row
   With CreateObject("scripting.dictionary")
      For Lc = 1 To RowCount2
         Vlu = Ws2.Range("A" & Lc) & Ws2.Range("C" & Lc)
         .Item(Vlu) = Empty
      Next Lc
   
      For Lc = 1 To RowCount
         Vlu = Ws1.Range("A" & Lc) & Ws1.Range("C" & Lc)
         If .exists(Vlu) Then Cells(Lc, 1).EntireRow.Interior.Color = vbRed
      Next Lc
   End With
End Sub
Hi VincentNL99,

That for the reply. I added Dim RowCount As Double, Dim RowCount2 As Double as the program was stating the variable was not defined. The program run with no debug errors but I am not seeing any rows with color changes. Did you need me to do something else?
 

VincentNL99

New Member
Joined
Sep 19, 2021
Messages
32
Office Version
  1. 365
Platform
  1. Windows
Hi VincentNL99,

That for the reply. I added Dim RowCount As Double, Dim RowCount2 As Double as the program was stating the variable was not defined. The program run with no debug errors but I am not seeing any rows with color changes. Did you need me to do something else?
I would dim as integer. Are you using it in an userform?

Edited code:
VBA Code:
Sub CheckRows()
   Dim cl As Range
   Dim Ws1 As Worksheet
   Dim Ws2 As Worksheet
   Dim Vlu As String
   Dim rc As Integer
   Dim RowCount As Integer
   Dim RowCount2 As Integer
  
 
 
   Set Ws1 = Sheets("Sheet1")
   Set Ws2 = Sheets("sheet2")
   RowCount = Ws1.Cells(Ws1.Rows.Count, "A").End(xlUp).Row
   RowCount2 = Ws1.Cells(Ws1.Rows.Count, "A").End(xlUp).Row
   With CreateObject("scripting.dictionary")
      For rc = 1 To RowCount2
         Vlu = Ws2.Range("A" & rc) & Ws2.Range("C" & rc)
         .Item(Vlu) = Empty
      Next rc
  
      For rc = 1 To RowCount
         Vlu = Ws1.Range("A" & rc) & Ws1.Range("C" & rc)
         If .exists(Vlu) Then
            Cells(rc, 1).EntireRow.Interior.Color = vbGreen
            Else
            Cells(rc, 1).EntireRow.Interior.Color = vbRed
         End If
      Next rc
   End With
End Sub

If it doesn't work please upload the data you're using it on :)
 

scubadivingfool

New Member
Joined
Jun 17, 2010
Messages
35
Hi VincentNL99,

Almost works. I am using Module for the VBA. The rows that are correct are showing in blue and all others are showing in red. However if the row in sheet1 does not exist in sheet2 I don't want it to show any color. The end goal of the workbook will be to print out just the incorrect rows onto labels. How this helps and again thanks for helping out.

Sheet1
783/UCF48AHCEA5059.2N1RegularCGYSR-COEquipment
783/UCR48AHCEA4345.71S4RegularEquipment
783/WTRCS48HC(FLAT-TOP)EA10654.8S4Regular!BLANKEquipment
817/C10EA1271.14S1RegularCGYSR-3Equipment
817/C12EA1676.08S1RegularCGYSR-3Equipment
817/C9EA1115.99S1RegularCGYSR-3Equipment
817/CM12EA1399.19S4RegularCGYSR-3Equipment
817/CM-22EA1607.86S4RegularCGYSR-3Equipment
817/G10EA160031.13S4RegularCGYSR-3Equipment
817/G10ea160031.13N1RegularCGYSR-COSmallwares
817/G12EA2005.15S1RegularCGYSR-3Equipment
817/PF10E-CEA717.42S1RegularCGYSR-6Equipment
817/PP5EA547.54S4RegularCGYSR-5Equipment
817/RC1EA282.5S1RegularCGYSR-6Equipment
817/S13EA4735.38N2RegularCGYSR-3Equipment
817/SP-20EA3781.49S1Regular!BLANKEquipment
817/SP-5EA1207.09S1RegularCGYSR-7Equipment
817/SP-8EA1884.76S1RegularCGYSR-7Equipment
839/UM-RTU500-1EA34.16S1Regular!BLANKJanitorial
839/UM-RTU500-4EA86.88S1Regular!BLANKJanitorial
839/UM-ULTRA ATOMER IIEA303.48STOtherCGYSR-24Equipment
839/UM-ULTRA ATOMER II-DEA225.44STOther!BLANKEquipment
904/630100-001EA405.59S1RegularCGYSR-3Equipment
929/324SCEA21.74N2RegularCGYSR-21Furniture
929/379SCPK0.56S1RegularCGYSR-22Smallwares
929/494SCPK11.55S1RegularCGYSR-21Furniture
929/50100KIT70.52S4Regular!BLANKJanitorial
929/651SC(RH)EA9.46SXRegularCGYSR-20Smallwares
929/DT133(RH)EA19.3SXRegular!BLANKSmallwares
929/DT134(RH)EA55.23SXRegular!BLANKSmallwares
929/DT202EA118.17S2Regular!BLANKSmallwares
929/GL100EA38.54S1RegularCGYSR-21PPE
929/GL101EA38.54S1RegularCGYSR-21PPE
929/GL102EA28.9S1RegularCGYSR-21PPE
929/GL103EA38.54S1RegularCGYSR-21PPE
929/GL104EA38.54S1RegularCGYSR-21PPE
929/LF720EA36.41S4RegularCGYSR-21Smallwares
929/LF728EA27.02S4RegularCGYSR-21Tabletop


Sheet2
817/C12$4.00163005.05
817/G1010" MANUAL SLICER, 1/3HP160031.13
817/G1212" MED DUTY MANUAL SLICER 1/2HP200005.15
817/S13SLICER 13" HD MANUAL ANODIZE ALUM580063.85
1595/IMP-SPHSPHERICATOR10063.84
1595/SA15USSANSAIRE SOUS VIDE20097.88
904/630100-001SOUS VIDE IMMERSION CIRCULATOR432.63
582/SV-96DOMESTIC SOUS-VIDE THERMAL CIRCULAT259.62
582-SV-120SOUS-VIDE THERMAL CIRCULATOR288.44
582/CHINOOK 14VACUUM MACHINE 13.5" SEAL BAR3436.35
582/CHINOOK 14+VACUUM MACHINE 13.5" SEAL BAR5267.48
817/C99" MANUAL SLICER 1/4 HP (GC9)0
817/C1010" MANUAL SLICER 1/4 HP (GC10)1271.14
817/CM-22MEAT GRINDER1607.86
817/CM12GRINDER MEAT #12 HUB 1HP1399.19
582/SB90-0810SMOOTH VACUUM BAGS 10" X 14" -100BG17.33
582/SB90-08128X12" SMOOTH VACUUM BAGS26.16
582/SB90-1014SMOOTH VACUUM BAGS 10" X 14" -100BG36.39
817/C1241630005.05
817/G1010" MANUAL SLICER, 1/3HP1600031.13
817/G1212" MED DUTY MANUAL SLICER 1/2HP2000005.15
817/S13SLICER 13" HD MANUAL ANODIZE ALUM5800063.85
1595/IMP-SPHSPHERICATOR100063.84
1595/SA15USSANSAIRE SOUS VIDE2000097.88
904/630100-001SOUS VIDE IMMERSION CIRCULATOR43002.63
582/SV-96DOMESTIC SOUS-VIDE THERMAL CIRCULAT25009.62
582-SV-120SOUS-VIDE THERMAL CIRCULATOR28008.44
582/CHINOOK 14VACUUM MACHINE 13.5" SEAL BAR340036.35
582/CHINOOK 14+VACUUM MACHINE 13.5" SEAL BAR5267.48
817/C99" MANUAL SLICER 1/4 HP (GC9)5345363463
817/C1010" MANUAL SLICER 1/4 HP (GC10)1271.14
817/CM-22MEAT GRINDER1607.86
817/CM12GRINDER MEAT #12 HUB 1HP1399.19
582/SB90-0810SMOOTH VACUUM BAGS 10" X 14" -100BG17.33
582/SB90-08128X12" SMOOTH VACUUM BAGS26.16
582/SB90-1014SMOOTH VACUUM BAGS 10" X 14" -100BG36.39
817/C1241635.05
817/G1010" MANUAL SLICER, 1/3HP1631.13
817/G1212" MED DUTY MANUAL SLICER 1/2HP2005.15
817/S13SLICER 13" HD MANUAL ANODIZE ALUM5863.85
1595/IMP-SPHSPHERICATOR163.84
1595/SA15USSANSAIRE SOUS VIDE297.88
904/630100-001SOUS VIDE IMMERSION CIRCULATOR432.63
582/SV-96DOMESTIC SOUS-VIDE THERMAL CIRCULAT259.62
582-SV-120SOUS-VIDE THERMAL CIRCULATOR288.44
582/CHINOOK 14VACUUM MACHINE 13.5" SEAL BAR3436.35
582/CHINOOK 14+VACUUM MACHINE 13.5" SEAL BAR5267.48
817/C99" MANUAL SLICER 1/4 HP (GC9)0
817/C1010" MANUAL SLICER 1/4 HP (GC10)1271.14
817/CM-22MEAT GRINDER1607.86
817/CM12GRINDER MEAT #12 HUB 1HP1399.19
582/SB90-0810SMOOTH VACUUM BAGS 10" X 14" -100BG17.33
582/SB90-08128X12" SMOOTH VACUUM BAGS26.16
582/SB90-1014SMOOTH VACUUM BAGS 10" X 14" -100BG36.39
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,212
Messages
5,836,031
Members
430,399
Latest member
Hayy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top