compare two excel sheets

[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.

 

[StuckInTheMiddle]

Your IF ... THEN ... ELSE statement makes no sense, you have 2 ELSEs which is part of your problem, you can only do 1 ELSE, I'd suggest changing this to an IF THEN ELSEIF and check for a 2nd condition.

A,

 

[HyperEngineer]

StuckIntheMiddle is right.

Maybe you want

    If ws1.Cells(i, 8) = ws2.Cells(j, 8) Then
       ws1.Cells(i, 1).Font.ColorIndex = 5    'blue
    ElseIf ws1.Cells(i, 8) > ws2.Cells(j, 8) then
       ws1.Cells(i, 1).Font.ColorIndex = 10    'green
    Else
       ws1.Cells(i, 1).Font.ColorIndex = 3    'red
    End If

HyperEngineer
If it ain't broke, it probably needs improvement.

 

[jozino01]

sorry, that double ELSE was a mistake during copy&paste. there was only a logical problem in the actual code, no syntax error.

eventualy, this code works:

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