comparison

[franq]

Hi
Could anyone help me with some advice about comparison operators.
I need to program below but i just dont know how. I have begun studying VBA programming but a solution is evasive.


OLD RANGE NEW RANGE
Code Price Code Price
a1 100 a1 102
b1 201 b1 450
c1 250 c1 250
d1 188
e1 418 e1 399
f1 900 f1 899
g1 500 g1 500
h1 300 //This is new product


1 Take column A Product code and look for Duplicate Code in Column D. (could also be in different worksheets than I don’t have to paste it)
2 Compare prices
3 If prices differ mark it orange
4 When Code does not exist in new Range mark old code RED (this product should be deleted)
5 When Code exist in New Range but not in old mark this as blue
6 (Optional) When prices differ by more than R50 mark new price in Orange rather than yellow

(The tables does not display right sorry)

Excel 2007/Win 7

 

[John Davis]

Hi
Could anyone help me with some advice about comparison operators.
I need to program below but i just dont know how. I have begun studying VBA programming but a solution is evasive.


OLD RANGE NEW RANGE
Code Price Code Price
a1 100 a1 102
b1 201 b1 450
c1 250 c1 250
d1 188
e1 418 e1 399
f1 900 f1 899
g1 500 g1 500
h1 300 //This is new product


1 Take column A Product code and look for Duplicate Code in Column D. (could also be in different worksheets than I don’t have to paste it)
2 Compare prices
3 If prices differ mark it orange
4 When Code does not exist in new Range mark old code RED (this product should be deleted)
5 When Code exist in New Range but not in old mark this as blue
6 (Optional) When prices differ by more than R50 mark new price in Orange rather than yellow

(The tables does not display right sorry)

Excel 2007/Win 7

May need some work but, does something like this help or point you in the right direction?

Sub franq()
Dim i As Long
Dim lr As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

With Range("E3:E10")

    .Formula = "=VLOOKUP(C3,$A$3:$B$10,2,false)"
    .Value = .Value
    .Replace What:="#N/A", Replacement:="", LookAt:=xlWhole
    
End With

For i = lr To 3 Step -1

    If Range("E" & i) = "" Then Range("C" & i).Interior.ColorIndex = 3

    If Range("E" & i) <> "" And Range("D" & i) <> Range("E" & i) Then
    
        Range("C" & i).Interior.ColorIndex = 45
        
    End If
   
Next i

With Range("E3:E10")

    .Formula = "=VLOOKUP(A3,$C$3:$D$10,2,false)"
    .Value = .Value
    .Replace What:="#N/A", Replacement:="", LookAt:=xlWhole
    
End With

For i = lr To 3 Step -1

    If Range("E" & i) = "" Then Range("a" & i).Interior.ColorIndex = 5

    
Next i


End Sub

 

[franq]

Hi John
thanks so much

 

[John Davis]

Hi John
thanks so much

You're welcome. Glad it helps.