Compare - Help me modify script

[emukiss10]

Hi,

I would like to look for contents of cells in column A Sheet 1 and compare it to contents in Sheet 2.

If it can be found (ex. A6 sheet 1 was found in A99 sheet 2) than I want to compare column C in both sheets. If its not match - remove row in sheet 2.

my code to compare and remove unique values from Sheet 2

   Dim Cl As Range
   Dim Rng As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Sheets(1).Range("A2", Sheets(1).Range("A" & Rows.Count).End(xlUp))
         If Not .Exists(Cl.Value) Then .Add Cl.Value, Nothing
      Next Cl
      For Each Cl In Sheets(2).Range("A2", Sheets(2).Range("A" & Rows.Count).End(xlUp))
         If Not .Exists(Cl.Value) Then
            If Rng Is Nothing Then
               Set Rng = Cl
            Else
               Set Rng = Union(Rng, Cl)
            End If
         End If
      Next Cl
   End With
   If Not Rng Is Nothing Then Rng.EntireRow.Delete

Id like it to "IF" value is found check column C if it is a match too.


Best regards
w.

 

[Fluff]

Untested, but how about

Sub chk()
   Dim Cl As Range
   Dim Rng As Range
   Dim ValU As String
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Sheets(1).Range("A2", Sheets(1).Range("A" & Rows.Count).End(xlUp))
         ValU = Cl.Value & Cl.Offset(, 2).Value
         If Not .Exists(ValU) Then .Add ValU, Nothing
      Next Cl
      For Each Cl In Sheets(2).Range("A2", Sheets(2).Range("A" & Rows.Count).End(xlUp))
         ValU = Cl.Value & Cl.Offset(, 2).Value
         If Not .Exists(ValU) Then
            If Rng Is Nothing Then
               Set Rng = Cl
            Else
               Set Rng = Union(Rng, Cl)
            End If
         End If
      Next Cl
   End With
   If Not Rng Is Nothing Then Rng.EntireRow.Delete
End Sub

 

[emukiss10]

I think it works.

Can it be a liitle different at the end? Not to delete but color entire row from A to last used column (or in my case N)?

 

[emukiss10]

Ive done it for entire row but Id like to stop at last column used

 If Not Rng Is Nothing Then Rng.EntireRow.Interior.Color = RGB(172, 153, 207)

The big Question:

Can I modify it (add more variables) to check C1 with different columns at once? Not just column C? Like 5 or 6 more?

 

[Fluff]

Deleted
seen post#4

 

[Fluff]

How about

Sub chk()
   Dim Cl As Range
   Dim ValU As String
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Sheets(1).Range("A2", Sheets(1).Range("A" & Rows.Count).End(xlUp))
         ValU = [COLOR=#0000ff]Cl.Value & Cl.Offset(, 2).Value & Cl.Offset(, 3)[/COLOR]
         If Not .Exists(ValU) Then .Add ValU, Nothing
      Next Cl
      For Each Cl In Sheets(2).Range("A2", Sheets(2).Range("A" & Rows.Count).End(xlUp))
         ValU =[COLOR=#0000ff] Cl.Value & Cl.Offset(, 2).Value & Cl.Offset(, 3)[/COLOR]
         If Not .Exists(ValU) Then
            Cl.Resize(, 14).Interior.Color = RGB(172, 153, 207)
         End If
      Next Cl
   End With
End Sub

This checks the values in cols A, C & D. To add more columns change the lines in blue, by adding more Cl.offset in the same way

 

[emukiss10]

Thank You. You are always the Best!

ps. can i color not entire row but row from column A to column N?

 

[Fluff]

That's what it does

 

[emukiss10]

Yup, I did not seen that change Thank You!

 

[Fluff]

You're welcome