Delete Specific Data using Excel VBA

A website visitor has an interesting question:
My sheet1 has the following records in Excel 2007:
Code Item dim qty price
1001 Ball 7" 1 100
1002 Belt 36" 1 150
1003 Chain 12" 1 50

Now in sheet2 in any of the cell, say from A1 to A10 if I mention
just 1001 and enter then in sheet1 the entire row from Item to Price
should become blank. But the number in cell should not be deleted (i.e. 1001)
Now in sheet2 again in cell A7 I give 1003 then in sheet1 the entire row
from Item to Price should become blank. But the number in cell should not be deleted (i.e. 1007)
Looking out for the solution for this. Tried a lot but unable to do it.
If you write a macro in a specific sheet, say sheet1, then you may have a problem running the code because in this case you also need to access sheet2. Therefore you need a stand alone module.
First we find out which code the user enters in a worksheet2 and capture it in a variable. Next we go to sheet one and loop through its data and compare the code numbers with the code number captured in the variable 'num'. If we find the code we then select the appropriate row and columns and delete the specific data like for example in columns 2 to 5. Here we combine the interesting property of the 'Range' and the 'cells property' to access the correct column and row range. Once the data is accessed we can then clear the contents only of those cells in a specific manner.
Once you study the video you'll realize that it has covered many interesting aspects of Excel VBA.
Also you can assign a 'shortcut' to the macro such as 'Ctrl+m'.
You can see more details here:
https://www.exceltrainingvideos.com/delete-specific-data-from-an-excel-worksheet-using-vba/