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

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

"(E-Mail Removed)" wrote:

> 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
>
>

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

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

Hope this helps,

Hutch

"(E-Mail Removed)" wrote:

> 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
>
>

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

Michael