Find column in another column in exact row order

[lmaocopter]

Hey, i'm struggling to make a macro which looks up column made out of 5-90rows in another column in exactly same positions.
example:
column 1:
asd
asd2
dfd
dfd
33h8
reg3
sfd
11d
ads6
hef3
fe2
hrt6
rhw4
2f1
fef0
jj

column 2:
reg3
sdf
11d
ads6

In current state that'd find only 1 column made out of exact order of the rows. I need to find every other possible appeareance of column 2. Can you please help me figure it out? (column 1 has 5000+ rows and i need to repeat this for 50 versions of column 2)

 

[Sahitya]

Hey, i'm struggling to make a macro which looks up column made out of 5-90rows in another column in exactly same positions.
example:
column 1:
asd
asd2
dfd
dfd
33h8
reg3
sfd
11d
ads6
hef3
fe2
hrt6
rhw4
2f1
fef0
jj

column 2:
reg3
sdf
11d
ads6

In current state that'd find only 1 column made out of exact order of the rows. I need to find every other possible appeareance of column 2. Can you please help me figure it out? (column 1 has 5000+ rows and i need to repeat this for 50 versions of column 2)

If you just want to know only if data present in Column A is present in B, you can use Conditional Formatting. It will highlight the same appearance. Please confirm whether my understanding is correct

 

[Rick Rothstein]

In current state that'd find only 1 column made out of exact order of the rows. I need to find every other possible appeareance of column 2.

It is not clear from your post what you mean by "find"... exactly what should the macro physically do? Do you want it to make the matching cells bold, display a list of addresses for the for first cell for each matching set of cells, display a count of the number of ranges found, something else? Please clarify exactly what you want the macro to do.

 

[lmaocopter]

Mark them in color, conditional formatting won't mark exact numbers of rows (eg. if column 1 contains exact order of 24 rows), it highlights every single row one by one, because any row value in column 2 can be out of the order, in column 1 too. And i need only exact order.

 

[Rick Rothstein]

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub ColorExactRangeMatches()
  Dim R As Long, X As Long, Rng As Range, DoNotColorIt As Boolean
  Set Rng = Range("B1", Cells(Rows.Count, "B").End(xlUp))
  For R = 1 To Cells(Rows.Count, "A").End(xlUp).Row
    If Cells(R, "A").Value = Rng(1).Value Then
      For X = R To R + Rng.Rows.Count - 1
        If Cells(X, "A").Value <> Rng(1).Offset(X - R).Value Then
          DoNotColorIt = True
          Exit For
        End If
      Next
      If Not DoNotColorIt Then Cells(R, "A").Resize(Rng.Rows.Count).Interior.Color = vbRed
      DoNotColorIt = False
    End If
  Next
End Sub[/td]
[/tr]
[/table]

 

[Rick Rothstein]

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub ColorExactRangeMatches()
  Dim R As Long, X As Long, Rng As Range, DoNotColorIt As Boolean
  Set Rng = Range("B1", Cells(Rows.Count, "B").End(xlUp))
  [B][COLOR="#0000FF"]Application.ScreenUpdating = False[/COLOR][/B]
  For R = 1 To Cells(Rows.Count, "A").End(xlUp).Row
    If Cells(R, "A").Value = Rng(1).Value Then
      For X = R To R + Rng.Rows.Count - 1
        If Cells(X, "A").Value <> Rng(1).Offset(X - R).Value Then
          DoNotColorIt = True
          Exit For
        End If
      Next
      If Not DoNotColorIt Then Cells(R, "A").Resize(Rng.Rows.Count).Interior.Color = vbRed
      DoNotColorIt = False
    End If
  Next
  [B][COLOR="#0000FF"]Application.ScreenUpdating = True[/COLOR][/B]
End Sub[/td]
[/tr]
[/table]

I added a couple of lines of code (shown in blue above) to help speed up the code's execution.

 

[lmaocopter]

I've been struggling with this for 4 days, i'm not much into macros but still learning. Thank you very much