Recording cell value (formula) as it changes using VBA

[ExcelColonist]

Good evening,

I have two sheets, DataSheet and CalcSheet, in CalcSheet there are 2 cells in table2 (B2, C2, both formula's) whose value's are calculated based off cells in a table1 in DataSheet (where I input and add new data as I go)

In CalcSheet, I'm hoping to figure out how to record cells B2 and C2 in two columns in the same sheet, as they change. For example,

if cell B2 = 5, store value 5 in cell D1
if cell B2 = 10, store value 10 in cell D2
if cell B2 = 55, store value 55 in cell D3

if cell C2 = 4, store value 4 in cell E1
if cell C2 = 17, store value 17 in cell E2
if cell C2 = 45, store value 45 in cell E3 and so forth.

I did some homework and found a helpful guide here: How to record changing values in a cell in Excel? (extendoffice.com) But it doesn't quite explain what I'm hoping to achieve.

 

[Peter_SSs]

Welcome to the MrExcel board!

What is the formula in B2 of CalcSheet?
What is the formula in C2 of CalcSheet?

 

[mohadin]

What about
This code in CalcSheet code

Private Sub Worksheet_Calculate()
    Me.Cells(2, 4).Offset(Me.Cells(Rows.Count, 5).End(xlUp).Row - 1) = Me.Range("B2").Value
    Me.Cells(2, 5).Offset(Me.Cells(Rows.Count, 6).End(xlUp).Row - 1) = Me.Range("c2").Value
End Sub