none
Excel: Cell value RRS feed

  • Question

  • Dear all,

    I want to track the contents of a cell.
    say eg: a cell A4 has a value of 25, and now I delete 25 and put 35 in the same cell,

    I want to note both the values 25 and 35 and store these values in a different part of the same spread sheet.
    I want to maintain the same sheet through out the year and note all the changes including date and time the values are changed.

    Can any one help me please...

    With Regards
    Repath Athyala.


    Love the Love that Loves the Love and Hate the Love that Loves the Hate.
    Monday, February 7, 2011 9:48 AM

Answers

  • Enter the following Event macro in the worksheet code area:

     

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim A4 As Range, Log As Long, N As Long
    Set A4 = Range("A4")
    Log = 2
    Application.EnableEvents = False
    If Cells(1, Log).Value = "" Then
        Cells(1, Log).Value = A4.Value
        Cells(1, Log + 1).Value = Now
    Else
        N = Cells(Rows.Count, Log).End(xlUp).Row + 1
        Cells(N, Log).Value = A4.Value
        Cells(N, Log + 1).Value = Now
    End If
    Application.EnableEvents = True
    End Sub

    This example uses columns B & C to log the changes.  To use different columns, change  Log.

     

     
    Because it is worksheet code, it is very easy to install and automatic to use:
     
    1. right-click the tab name near the bottom of the Excel window
    2. select View Code - this brings up a VBE window
    3. paste the stuff in and close the VBE window
     
    If you have any concerns, first try it on a trial worksheet.
     
    If you save the workbook, the macro will be saved with it.
     
     
    To remove the macro:
     
    1. bring up the VBE windows as above
    2. clear the code out
    3. close the VBE window
     
    To learn more about macros in general, see:
     
    http://www.mvps.org/dmcritchie/excel/getstarted.htm
     
    To learn more about Event Macros (worksheet code), see:
     
    http://www.mvps.org/dmcritchie/excel/event.htm

    gsnu201011
    • Marked as answer by Repath Athyala Monday, February 7, 2011 12:54 PM
    Monday, February 7, 2011 12:38 PM
    Moderator

All replies

  • Hi Repath,

    You can code it in the worksheet change event

    Private Sub Worksheet_Change(ByVal Target As Range)

    ...

    End Sub

    You'll need to first think of where and how you'll store this information.


    Tiago Cardoso VB / VBA Analyst
    Monday, February 7, 2011 12:33 PM
  • Enter the following Event macro in the worksheet code area:

     

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim A4 As Range, Log As Long, N As Long
    Set A4 = Range("A4")
    Log = 2
    Application.EnableEvents = False
    If Cells(1, Log).Value = "" Then
        Cells(1, Log).Value = A4.Value
        Cells(1, Log + 1).Value = Now
    Else
        N = Cells(Rows.Count, Log).End(xlUp).Row + 1
        Cells(N, Log).Value = A4.Value
        Cells(N, Log + 1).Value = Now
    End If
    Application.EnableEvents = True
    End Sub

    This example uses columns B & C to log the changes.  To use different columns, change  Log.

     

     
    Because it is worksheet code, it is very easy to install and automatic to use:
     
    1. right-click the tab name near the bottom of the Excel window
    2. select View Code - this brings up a VBE window
    3. paste the stuff in and close the VBE window
     
    If you have any concerns, first try it on a trial worksheet.
     
    If you save the workbook, the macro will be saved with it.
     
     
    To remove the macro:
     
    1. bring up the VBE windows as above
    2. clear the code out
    3. close the VBE window
     
    To learn more about macros in general, see:
     
    http://www.mvps.org/dmcritchie/excel/getstarted.htm
     
    To learn more about Event Macros (worksheet code), see:
     
    http://www.mvps.org/dmcritchie/excel/event.htm

    gsnu201011
    • Marked as answer by Repath Athyala Monday, February 7, 2011 12:54 PM
    Monday, February 7, 2011 12:38 PM
    Moderator