When content of a cell changes, content of another deletes

[mrlanier]

If a formulated value in A1 changes to any value after a value is
manually entered in B1, I need the content of B1 to be deleted. In
other words, 1) A1 first reflects a formulated value, 2) a value is
manually entered into B1, 3) input elsewhere causes A1 to return a
different calculated value, 4) thus causing B1 to delete its input.
Is there a simple macro that will accomplish this? Thanks in advance.

Michael

 

[Guest]

Paste the following code into the sheet module in the VBA Editor for the
worksheet where you want this to happen (Alt-F11 to open the VBA Editor, then
Ctrl-R to display the Project Explorer. For Sheet1, click on Sheet1, etc.)

Private LastVal

Private Sub Worksheet_Activate()
LastVal = Range("A1").Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$1" Then Exit Sub
If Range("A1").Value <> LastVal Then
Range("B1").Delete
LastVal = Range("A1").Value
End If
End Sub

Hope this helps,

Hutch

 

[mrlanier]

Hutch,

Your macro works great but with one problem. There are other cells in
the same column as B1. When B1's contents delete, the cells beneath
B1 change position. For example, B10 repositions to become B9, and
then B8 with the next change to the contents of A1. They continue to
migrate one row at a time. Do you have a further suggestion? Thanks.

Michael

 

[Guest]

Replace Range("B1").Delete with
Range("B1").ClearContents

Hope this helps,

Hutch

 

[mrlanier]

Thanks Hutch. It appears to be working just as anticipated.

Michael