identifying and moving matching values

[sean_f]

Hi,
I need to read through the file belowidentify those records that have a
matching code and balance and move them to a second spreadsheet.

The moving isn't a problem but how do I filter based on the match as
given?

If I should read through the code how would I set the variables to
check
for?
Also if I use a loop how will I know when it has finished?

Many thanks

sean
Code--- Buy/Sell--------Currency-- Balance--
AA001--Buyer---------------USD------"33,790"--
AA001--Seller--------------- USD------"33,790"--
AA002--Buyer---------------AUD------"773,490"--
AA002--Seller--------------- AUD------"773,490"--
AA003--Buyer---------------CAD------"7,616,750"--
AA004--Buyer---------------DKK------"576,770"--
AA005--Buyer---------------USD------"24,090,430"--
AA005--Seller--------------- USD------"24,090,430"--
AA006--Buyer---------------EUR------"468,126,330"--
AA006--Seller--------------- EUR---- "468,126,330"--
AA007--Buyer---------------HKD------"10,000"--
AA008--Buyer---------------JPY------"2,435,917,410"--
AA008--Seller--------------- JPY------"2,435,917,410"--
AA009--Buyer---------------MXN------"1,002,670"--
AA009--Seller--------------- MXN------"1,002,670"--
--------- Seller--------------- CAD------"7,616,750"--
--------- Seller--------------- DKK------"576,770"--
--------- Seller--------------- HKD------"10,000"--

 

[sean_f]

The code below is intended to pick out the matching pairs and color
them blue. However it seems to act at random.
Anyone have any suggestions. As to how to find cells that are identical
and in adjacent rows I would be very grateful.

Sub matching()
Dim lastrow As Long
lastrow = Cells(65536, 1).End(xlUp).Row


For i = 2 To lastrow
If Cells(i, 1).Value = Cells(i - 1, 1).Value And Cells(i, 4).Value =
Cells(i - 1, 4) Then
Cells(i, 1).Resize(2, 5).Font.ColorIndex = 11
Cells(i - 1, 1).Resize(2, 5).Font.ColorIndex = 11
End If
Next i


End Sub

 

[sean_f]

The code below is intended to pick out the matching pairs and color
them blue. However it seems to act at random.
Anyone have any suggestions. As to how to find cells that are identical
and in adjacent rows I would be very grateful.

Sub matching()
Dim lastrow As Long
lastrow = Cells(65536, 1).End(xlUp).Row


For i = 2 To lastrow
If Cells(i, 1).Value = Cells(i - 1, 1).Value And Cells(i, 4).Value =
Cells(i - 1, 4) Then
Cells(i, 1).Resize(2, 5).Font.ColorIndex = 11
Cells(i - 1, 1).Resize(2, 5).Font.ColorIndex = 11
End If
Next i


End Sub