compare two excel worksheets

[jozino01]

hi,
i have a short and simple vb6 program, which compares two excel spreadheets containing month-end inventory. each spreadsheet contains item number (column 1) and volume on hand (column 8).
i try to add one function - mark items sold in previous month. the logic is following:
- program reads all items from spreadsheet1 (last month inventory) one by one and compares it to each item from spreadsheet2 (current inventory)
- if the item is not in current inventory, it assumes it's sold and changes the the font of item to red
- if the item is in current inventory but volume is different, it assumes it's partly sold and changes the the font of item to green
- if the item is in current inventory and volume is the same, it assumes it isn't sold and changes the the font of item to blue

but it doesn't work properly, it marks everything red.

here is the code:
For i = 1 To 99
Label1.Caption = i
If ws1.Cells(i, 7) = "GRN" Then
For j = 1 To 99
Label2.Caption = j
If ws2.Cells(j, 1) = ws1.Cells(i, 1) Then
If ws1.Cells(i, 8) = ws2.Cells(j, 8) Then
ws1.Cells(i, 1).Font.ColorIndex = 5 'blue
Else
ws1.Cells(i, 1).Font.ColorIndex = 10 'green
Else
ws1.Cells(i, 1).Font.ColorIndex = 3 'red
End If

End If
Next j
End If
Next i


any sugestion what is the best way to fix it?
thanks

note: items are not in the same order.

 

[SkipVought]

Hi,

If your tables are really tables, I'd use MS Query to compare them.

That would not give you pretty color, but it can report exclusions.

Check out Data/Get External Data/New Database Query -- Excel files -- YOUR WORKBOOK....

Can be done with probably no VBA code at all.

Skip,

_{[red]Be Advised![/red] Coeds studying ancient Egyptian plumbing, might be known as...
Pharaoh Faucet Majors}

 

[PHV]

For i = 1 To 99
Label1.Caption = i
If ws1.Cells(i, 7) = "GRN" Then
[!]ws1.Cells(i, 1).Font.ColorIndex = 3 'red[/!]
For j = 1 To 99
If ws2.Cells(j, 1) = ws1.Cells(i, 1) Then
If ws1.Cells(i, 8) = ws2.Cells(j, 8) Then
ws1.Cells(i, 1).Font.ColorIndex = 5 'blue
Else
ws1.Cells(i, 1).Font.ColorIndex = 10 'green
[!]End If
Exit For[/!]
End If
Next j
End If
Next i

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jozino01]

SkipVought - well, the spreadsheets contains more data then one table so no all lines have the same structure

PHV - thanks for the code; i tried it and it seems working properly. thanks!