Find and replace as well as find and delete

[wal_verin]

Hi all, I saw a post by Trebor76 that has helped me to come a few steps forward, but i still have alot to solve and i need the help of some skilled VBA Excel users. I am quite at the basic level in Excel VBA and i will really be thankful if someone can take sometime to help me out

I have data in two sheets, termed Sheet 1 and Sheet 2 as shown below. The idea is that when the command button is pressed the macro should execute the following:
1. Search for the Product ID in Sheet 1 from Sheet 2, if it finds it in Sheet ,
It will only change the Amount found on Row 3 in Column C in Sheet 1 with the Amount on Sheet 2 and Column F. The output result of how it should look is shown below
2. If it does not find a Product ID in Sheet 1 that is found in Sheet 2, it should add it in Sheet 1 in Ascending order and have the same format in Sheet 1.
3. If it finds a Product ID in Sheet 1 that is not in Sheet 2, it should delete the corresponding rows. That is ONLY the first two rows connected to every Product ID
4. If it finds a Product ID in Sheet 1 and Sheet 2 with the same amount, it should not alter it (move to next)
Sheet 1
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Product ID[/TD]
[TD]Color/Type[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]10001[/TD]
[TD]White[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Cotton[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]10003[/TD]
[TD]Blue[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Cotton[/TD]
[TD]1500[/TD]
[/TR]
[TR]
[TD]10004[/TD]
[TD]Black[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Cotton[/TD]
[TD]4500[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]













Sheet 2
A-------------------B--------------C-----------D----------E------------F-------
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]10001[/TD]
[TD]White[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]1000[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]10002[/TD]
[TD]Purple[/TD]
[TD]1800[/TD]
[TD]900[/TD]
[TD]300[/TD]
[TD]3000[/TD]
[/TR]
[TR]
[TD]10003[/TD]
[TD]Blue[/TD]
[TD]1500[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1500[/TD]
[/TR]
[TR]
[TD]10004[/TD]
[TD]Black[/TD]
[TD]2100[/TD]
[TD]250[/TD]
[TD]1650[/TD]
[TD]4000[/TD]
[/TR]
</tbody>[/TABLE]







Output result = Notice that the Word Cotton does not change even with a new product id added
You will also notice that the Amounts replaced are from Column F
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Product ID[/TD]
[TD]Color/Type[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]10001[/TD]
[TD]White[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Cotton[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]10002[/TD]
[TD]Purple[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Cotton[/TD]
[TD]3000[/TD]
[/TR]
[TR]
[TD]10003[/TD]
[TD]Blue[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Cotton[/TD]
[TD]1500[/TD]
[/TR]
[TR]
[TD]10004[/TD]
[TD]Black[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Cotton[/TD]
[TD]4000[/TD]
[/TR]
</tbody>[/TABLE]