totn Excel

MS Excel 2003: Link one cell to another cell (control the recalculation)

This Excel tutorial explains how to write a macro to link one cell to another and control the recalculation in Excel 2003 and older versions (with screenshots and step-by-step instructions).

Question: In Microsoft 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 look at an example.

Download Excel spreadsheet (as demonstrated below)

Microsoft Excel

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