MS Excel: Link one cell to another cell (control the recalculation) in Excel 2003/XP/2000/97

Question: In Excel 2003/XP/2000/97, is there a way to link a cell in one column to a cell in another? For example, when I enter a memo number in column A, I need a date value to appear in column B (in the corresponding row).

Once the date value has been set in column A, I do not want the value to change. (ie: The date value should not change the next day when the file is opened.)

Answer: This can be accomplished using a macro.

Let's take a look at an example.

Download Excel spreadsheet (as demonstrated below)

On the sheet called Sheet1, we've placed code on the "Worksheet_Change" event, so that whenever the data in column A changes, the associated cell in column B will be updated.

You can press Alt-F11 to view the VBA code.

Note: The dates will re-calculate if you, for example, "insert a column before column A and then delete it". So make sure that your column structures are static before you rely on the linked date values.

Macro Code:

The macro code looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim LLoop As Integer

    Dim LTargetRange1 As String
    Dim LDestRange1 As String

    LLoop = 2

    'Check first 200 rows in spreadsheet
    While LLoop <= 200
        'Link column A to B
        LTargetRange1 = "A" & CStr(LLoop)
        LDestRange1 = "B" & CStr(LLoop)

        If Not Intersect(Range(LTargetRange1), Target) Is Nothing Then
            If Len(Range(LTargetRange1).Value) > 0 Then
                Range(LDestRange1).Value = Date
            Else
                Range(LDestRange1).Value = Null
            End If
        End If

        LLoop = LLoop + 1
    Wend

End Sub