Forcing A Cell To Change Using a Drop Down List

[rminor]

I am trying to force a cell to change its text. So Sheet 2 has six columns, Address in the A column and a Status in the B column. Column C, D, E, F, have information related to the address.
On Sheet 1 A1 I have a List box pulling the information from Sheet 2 Column A (Address), then vlookup formula to pull the information related to that particular address. i created another list box with a list of status on Sheet 1 D4 . So I'm trying to be select the status on Sheet 1 to permanently change the appropriate address status on Sheet 2 Column B.

This is the VB Code i have gotten so far.

Worksheets("Sheet2").Range("B2").Value = Worksheets("Sheet1").Range("D4").Value
Sheet2.Cells(2, 1).Value = 10

Sheet1 D4 has a drop down list of Status, when i change the Status from the dropdown list it changes the cell on Sheet 2 B2.

What i need to do, is get it to change the cell in Sheet2 Column B reference the address I choose in Sheet1 A1 and change the cell on Sheet2 Column B that is been referenced in Column A

 

[Fluff]

Duplicate Saving Text In A Specific Cell

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).