Old vs. new cell value comparison - possible?

[Closed]
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,

I need to track value changes in a cell as follows.
I have sheet 1 - current data and sheet 2 - copy of sheet 1 from yesterday.
I'd like to do 2 things.
1. if value in sheet1.a1 changes in any way; up or down, change current cell background to a different color (1 of 3; yellow, green, blue, in rotation).
2. if value in sheet1.a1 changes in any way; up or down, copy old value to sheet2.a1, overwriting old value there.

Can this be done reasonably easily?
I've been a programmer for years and am an oracle dba now, but am not current on vb or macros. Any detailed assistance would greatly be appreciated.

Thanks in advance!

1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
quote
different color (1 of 3; yellow, green, blue, in rotation).
unquote
I do not understand this why this color rotation. after all you need to know which is the current number

anyhow try this experiment.
open a new workbook
right click sheet1 tab and copy the event code given below
now you enter any number in A1(A1 only as this is the target cell) of sheet 1 repeat sheet1
now see sheet2
now enter another number in A1 sheet 1 and see sheet2
now enter another number in A1 sheet1 and see sheet 2
are you not satisfied with this

the event code is

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address <> "$A$1" Then GoTo line1
Target.Copy Worksheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
With Worksheets("sheet2")
.Cells.Interior.ColorIndex = xlNone
.Cells(Rows.Count, "A").End(xlUp).Interior.ColorIndex = 6
End With
line1:
Application.EnableEvents = True

End Sub