SNAPSHOT

[Cooki]

Hi All

Is there a way to get snapshot of a cell if it is changed.

So dropdown list in E1 and i want a snapshot of that cell if it is changed in Cell P1

Is this possible?

Thanks

 

[Cooki]

also I need it work same way for E2, E3 etc

 

[Yongle]

Picture ?
Yes you can use Excel's camera tool to take the snapshot, and automate it using some simple VBA
Do you want to retain successive snapshots?

Or value ?
- do you simply want the value in cells P1 (...etc) amended ?

How do you intend using the snapshots ?

 

[Yongle]

Place this code in the sheet code window
(right-click sheet tab \ View Code \ paste code into that window)
Amend the values a few times in E1 and E2 and tell me what requires changing

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("E1").Resize(Rows.Count)) Is Nothing Then
        With Target
            .Copy
            .Offset(, 11).Activate
            ActiveSheet.Pictures.Paste
            .Activate
        End With
    End If
End Sub

 

[Cooki]

Picture ?
Yes you can use Excel's camera tool to take the snapshot, and automate it using some simple VBA
Do you want to retain successive snapshots?

Or value ?
- do you simply want the value in cells P1 (...etc) amended ?

How do you intend using the snapshots ?

So the values in column E are tasks and if they are changed, I need to know what they used to be so that I can change it back.

if it is possible to get the value so that I could copy and paste it back into E1 if required.

 

[Yongle]

Try this

Change the value in E1 as before
Replace with value in E1 by right-click in either P1 or E1 etc

Delete previous code and replace with this

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("P1").Resize(Rows.Count)) Is Nothing Then
        Cancel = True
        Application.EnableEvents = False
        Target.Offset(, -11).Value = Target.Value
        Application.EnableEvents = True
    End If
   If Not Intersect(Target, Range("E1").Resize(Rows.Count)) Is Nothing Then
        Cancel = True
        Application.EnableEvents = False
        Target.Value = Target.Offset(, 11).Value
        Application.EnableEvents = True
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("E1").Resize(Rows.Count)) Is Nothing Then
        Target.Offset(, 11).Value = Target.Value
    End If
End Sub

 

[Cooki]

Place this code in the sheet code window
(right-click sheet tab \ View Code \ paste code into that window)
Amend the values a few times in E1 and E2 and tell me what requires changing

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("E1").Resize(Rows.Count)) Is Nothing Then
        With Target
            .Copy
            .Offset(, 11).Activate
            ActiveSheet.Pictures.Paste
            .Activate
        End With
    End If
End Sub

It works how you have said, but its not want I was thinkings

Sorry the information is in Column C, starting from C2 and goes down to C156

So what I need is, if the Cell is changed in C2, I need to know what it was previously, but I need the value on the same row just at the end of the columns, which is T.

So T2 would have the previous value of what was in C2

Try this

Change the value in E1 as before
Replace with value in E1 by right-click in either P1 or E1 etc

Delete previous code and replace with this

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("P1").Resize(Rows.Count)) Is Nothing Then
        Cancel = True
        Application.EnableEvents = False
        Target.Offset(, -11).Value = Target.Value
        Application.EnableEvents = True
    End If
   If Not Intersect(Target, Range("E1").Resize(Rows.Count)) Is Nothing Then
        Cancel = True
        Application.EnableEvents = False
        Target.Value = Target.Offset(, 11).Value
        Application.EnableEvents = True
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("E1").Resize(Rows.Count)) Is Nothing Then
        Target.Offset(, 11).Value = Target.Value
    End If
End Sub

Its just coping what ever value I type in there.

its problely me explain it wrong

So I need the snapshot of what ever was in that cell previously

example

C2 says "Task1" ------ T2 is blank as no changes have been made in C2

Now there is a change

C2 changes to "Task2" ----- T2 has the value of what was in C2 previously "Task1"

Does that made sense?

Apologies

 

[Yongle]

??
what has happened to columns P and E ?

 

[Yongle]

Perhaps this

'declare these 2 variables at top of sheet module ABOVE all procedures
Private oldvalue As String, Ref As String

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("C2:C156"), Target) Is Nothing Then
        If Target.Address = Ref Then
            Application.EnableEvents = False
            If oldvalue <> Target.Value Then Target.Offset(, 17).Value = oldvalue
            Application.EnableEvents = True
        End If
        oldvalue = ""
        Ref = ""
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.Count <> 1 Then Exit Sub
    If Not Intersect(Range("C2:C156"), Target) Is Nothing Then
        oldvalue = Target.Value
        Ref = Target.Address
    End If
End Sub

 

[Cooki]

??
what has happened to columns P and E ?

apologies i went out for walk and typed it from my phone, so hazard a guess on columns and cells

When i got back i noticed i put wrong cells in