Finding the order in which data was entered.....

[ExcelNovice]

All, I'm working on a large spreadsheet.The following cells starts out with zero:

HA125, HG125, HM125, HS125, HY125, IE125, IK125, IQ125, HA135, HG135, HM135, HS135, HY135, IE135, IK135, IQ135, HA145, HG145, HM145, HS145, HY145, IE145, IK145, IQ145, HA155, HG155, HM155, HS155, HY155, IE155, IK155, IQ155, HA165, HG165, HM165, HS165, HY165, IE165, IK165, IQ165, HA175, HG175, HM175, HS175, HY175, IE175, IK175, IQ175, HA185, HG185, HM185, HS185, HY185, IE185, IK185, IQ185, HA195, HG195, HM195, HS195, HY195, IE195, IK195, IQ195

The user will then enter the #6 data in each cell only once, but will enter the number 6 randomly. For instance, the first 6 may be entered in cell IK175, the second in cell HA125 and so on.

Question, is there a way to determine the order in which each 6 was entered? Which one was first, second, third and so on?

Thanks for your help.

 

[L. Howard]

Try this from the OZGrid.Com, copy & paste in the sheet module.

Probably gives you more info than you want, but some of it can be eliminated with small code changes.

You will need to set the last column and last row of the range you want to monitor, currently at Column 10 & Row 10.

Makes the list on Sheet 2, columns A to F. (Changeable)

Regards,
Howard

Option Explicit

Dim vOldVal 'Must be at top of module


Private Sub Worksheet_Change(ByVal Target As Range)

Dim bBold As Boolean

If Target.Cells.Count > 1 Then Exit Sub
If Target.Column > 10 Then Exit Sub
If Target.Row > 10 Then Exit Sub

On Error Resume Next

    With Application
         .ScreenUpdating = False
         .EnableEvents = False
    End With

    If IsEmpty(vOldVal) Then vOldVal = "Empty Cell"

    bBold = Target.HasFormula

        With Sheet1
            '.Unprotect Password:=""
                If Sheets("Sheet2").Range("A1") = vbNullString Then
                    Sheets("Sheet2").Range("A1:F1") = Array("CELL CHANGED", "OLD VALUE", "NEW VALUE", _
                     "USER NAME", "TIME OF CHANGE", "DATE OF CHANGE")
                End If

            With Sheets("Sheet2").Cells(.Rows.Count, 1).End(xlUp)(2, 1)
                  .Value = Target.Address
                  .Offset(0, 1) = vOldVal
                  .Offset(0, 3) = Application.UserName

                      With .Offset(0, 2)
                        If bBold = True Then
                          .ClearComments
                          .AddComment.Text Text:= _
                               "OzGrid.com:" & Chr(10) & "" & Chr(10) & _
                                  "Bold values are the results of formulas"
                        End If
                          .Value = Target
                          .Font.Bold = bBold
                      End With

                .Offset(0, 4) = Time
                .Offset(0, 5) = Date
                
            End With

            '.Cells.Columns.AutoFit
            '.Protect Password:=""

        End With

    vOldVal = vbNullString

    With Application
         .ScreenUpdating = True
         .EnableEvents = True
    End With

On Error GoTo 0

End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    vOldVal = Target

End Sub

 

[ExcelNovice]

L. Howard, this is a very helpful code. Thank you very much. I had a chance to review it and it will definitely help me work out a solution. Can you tell me how to tweak it a little?
Need to do the following:
1. How do I set the target range to monitor? Right it monitors all data entered within column 10 and row 10. How do I tweak the code so that it will monitor any range I set?
2. Also, how do I tweak it so that only a specific number (number 6, or 2, or 3 etc...) is monitored? For example, if the number entered in the range is > 6 then ignore it....

Thanks again for your help.

 

[L. Howard]

See the examples in red text to set a target value.

See the LAST column and LAST row numbers in green to set limits of the area to monitor.

Howard

Option Explicit

Dim vOldVal 'Must be at top of module


Private Sub Worksheet_Change(ByVal Target As Range)

[COLOR="#FF0000"]'If Target.Value > 6 Then Exit Sub
If Target.Value < 8 Then Exit Sub
'If Target.Value <> 2 Then Exit Sub
[/COLOR]

Dim bBold As Boolean

If Target.Cells.Count > 1 Then Exit Sub

If Target.Column > [COLOR="#00FF00"]10[/COLOR] Then Exit Sub
If Target.Row > [COLOR="#00FF00"]10[/COLOR] Then Exit Sub

On Error Resume Next

    With Application
         .ScreenUpdating = False
         .EnableEvents = False
    End With

    If IsEmpty(vOldVal) Then vOldVal = "Empty Cell"

    bBold = Target.HasFormula

        With Sheet1
            '.Unprotect Password:=""
                If Sheets("Sheet7").Range("A1") = vbNullString Then
                    Sheets("Sheet7").Range("A1:F1") = Array("CELL CHANGED", "OLD VALUE", "NEW VALUE", _
                     "USER NAME", "TIME OF CHANGE", "DATE OF CHANGE")
                End If

            With Sheets("Sheet7").Cells(.Rows.Count, 1).End(xlUp)(2, 1)
                  .Value = Target.Address
                  .Offset(0, 1) = vOldVal
                  .Offset(0, 3) = Application.UserName

                      With .Offset(0, 2)
                        If bBold = True Then
                          .ClearComments
                          .AddComment.Text Text:= _
                               "OzGrid.com:" & Chr(10) & "" & Chr(10) & _
                                  "Bold values are the results of formulas"
                        End If
                          .Value = Target
                          .Font.Bold = bBold
                      End With

                .Offset(0, 4) = Time
                .Offset(0, 5) = Date
                
            End With

            '.Cells.Columns.AutoFit
            '.Protect Password:=""

        End With

    vOldVal = vbNullString

    With Application
         .ScreenUpdating = True
         .EnableEvents = True
    End With

On Error GoTo 0

End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    vOldVal = Target

End Sub