Macro to make one cell copy the value of a cell that changes

[spiffmonkey1]

Hi,

I want a macro that runs automatically when a certain cell (C6
) changes. When C6 changes, I want D6 to copy the original value of C6 before the change.

Thanks

 

[Sektor]

Is C6 formula?

 

[spiffmonkey1]

Is C6 formula?

No, C6 would be a number

Thanks

 

[Sektor]

Just realized. What do you mean "original value"???

 

[spiffmonkey1]

In worksheet module.

[COLOR=blue]Private[/COLOR] [COLOR=blue]Sub[/COLOR] Worksheet_Change([COLOR=blue]ByVal[/COLOR] Target [COLOR=blue]As[/COLOR] Range)
    [COLOR=blue]If[/COLOR] Target.Address = Range("C6").Address [COLOR=blue]Then[/COLOR]
        Range("D6") = Range("C6")
    [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

Is it possible to make the cell D6 equal to the original value of C6. For example, if C6 = 30,000 and then the value was changed. D6 would then change to 30,000 because C6 had change to a new value

Thanks

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Variant
If Target.Address(False, False) = "C6" Then
    Application.EnableEvents = False
    x = Target.Value
    Application.Undo
    Target.Offset(, 1).Value = Target.Value
    Target.Value = x
    Application.EnableEvents = True
End If
End Sub

 

[spiffmonkey1]

Hi that seems to work.
Is it possible for me to target the undo value to another worksheet.
Like if I wanted to target D6 in worksheet 2 instead

Thanks!

When I tested the target to be a certain range or cell like-
Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Variant
If Target.Address(False, False) = "C6" Then
Application.EnableEvents = False
x = Target.Value
Application.Undo
Target.Cells(7, F).Value = Target.Value
Target.Value = x
Application.EnableEvents = True
End If
End Sub

It changes another cell
I just want to know the reason why so i can understand the macro

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Variant
If Target.Address(False, False) = "C6" Then
    Application.EnableEvents = False
    x = Target.Value
    Application.Undo
    Sheets("Sheet2").Range("D6").Value = Target.Value
    Target.Value = x
    Application.EnableEvents = True
End If
End Sub

 

[spiffmonkey1]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Variant
If Target.Address(False, False) = "C6" Then
    Application.EnableEvents = False
    x = Target.Value
    Application.Undo
    Sheets("Sheet2").Range("D6").Value = Target.Value
    Target.Value = x
    Application.EnableEvents = True
End If
End Sub

Awesome thanks so much!