Replace a cell in a row if its value is equal to a certain word

Hi,

This may be tough to explain but I will do my best (which also made it tough to find a similar thread before posting). Does anyone have a macro that would replace a cell if the value of that cell is the word "BALANCE" with the value from a cell in the same row. I have attached a sample spreadsheet, but this is a simple example:

LOT BALANCE SN CD B GA
100 91.9 .001 .01 <.001 BALANCE
101 98.1 BALANCE <.005 1.5 <.001

In the first row, I would want to replace the GA cell with 91.9. In the second row, I would want to replace SN with 98.1. So where the word balance would appear in that row, I want to replace it with the value in the "BALANCE" column of that row.

Please let me know if I need to clear anything up. Any help would be appreciated. Thanks.

This is perfect! Thank you very much.