Can I assign a formula to track when another cell is changed?
 

I am using Excel to assign students to different assignments during a
practical examination. I understand I can use "track changes" to monitor
changes to an individual cells. However, I will have forty individuals
participating in twelve different activities. As each person completes a
station, a number is assigned to the corresponding cell. It would be too
difficult to check each cell for the last modification time for each person.

I would like to have a cell that monitors when a change occurs within a
range of cells. This would help me in monitoring how long it has been since a
student has completed an assignment. Any suggestions would be useful.

Can I assign a formula to track when another cell is changed?
 

Would a Time Stamp work for you?
http://www.mcgimpsey.com/excel/timestamp.html

Or, this code can help you track changes:
Place under tab of Sheet2:
Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("a1:iv65536")
If Intersect(Target, r) Is Nothing Then Exit Sub
Application.EnableEvents = False
Cells(Target.Row, "Z").Value = Environ("username")
Application.EnableEvents = True
End Sub

Place under tab of Sheet1:
Dim vOldVal 'Must be at top of module
Private Sub Worksheet_Change(ByVal Target As Range)
Dim bBold As Boolean
Dim rArea As Range
Dim rCell As Range
For Each rArea In Target.Areas
For Each rCell In rArea
'your individual cell code here
Next rCell
Next rArea


If Target.Cells.Count 1 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:="Secret"
If .Range("A1") = vbNullString Then
.Range("A1:E1") = Array("CELL CHANGED", "OLD VALUE", _
"NEW VALUE", "TIME OF CHANGE", "DATE OF CHANGE")
End If


With .Cells(.Rows.Count, 1).End(xlUp)(2, 1)
.Value = Target.Address
.Offset(0, 1) = vOldVal
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, 3) = Time
.Offset(0, 4) = Date
End With
.Cells.Columns.AutoFit
'.Protect Password:="Secret"
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

HTH,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"richnlori1965" wrote:

I am using Excel to assign students to different assignments during a
practical examination. I understand I can use "track changes" to monitor
changes to an individual cells. However, I will have forty individuals
participating in twelve different activities. As each person completes a
station, a number is assigned to the corresponding cell. It would be too
difficult to check each cell for the last modification time for each person.

I would like to have a cell that monitors when a change occurs within a
range of cells. This would help me in monitoring how long it has been since a
student has completed an assignment. Any suggestions would be useful.

Can I assign a formula to track when another cell is changed?
 

Hy,
ryguy7272 ha scritto:
This part work for me: place my username on col. Z
on sheet2.
The problems the other one that I place on sheet1.
Nothing happen :(
First of all what I had to put where you write
'your individual cell code here ?
The cell I should check is A1:K114.
It wuold be very usefull to fill the array with the value of the array
plus the name of the sheet changed.
I hope make me clear! (sorry for the mistake i made for sure!!)
Thanks in Advance
Cla


Or, this code can help you track changes:
Place under tab of Sheet2:
Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("a1:iv65536")
If Intersect(Target, r) Is Nothing Then Exit Sub
Application.EnableEvents = False
Cells(Target.Row, "Z").Value = Environ("username")
Application.EnableEvents = True
End Sub

Place under tab of Sheet1:
Dim vOldVal 'Must be at top of module
Private Sub Worksheet_Change(ByVal Target As Range)
Dim bBold As Boolean
Dim rArea As Range
Dim rCell As Range
For Each rArea In Target.Areas
For Each rCell In rArea
'your individual cell code here
??? The cell i should check is A1:K114

Next rCell
Next rArea


If Target.Cells.Count 1 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:="Secret"
If .Range("A1") = vbNullString Then
.Range("A1:E1") = Array("CELL CHANGED", "OLD VALUE", _
"NEW VALUE", "TIME OF CHANGE", "DATE OF CHANGE")
End If


With .Cells(.Rows.Count, 1).End(xlUp)(2, 1)
.Value = Target.Address
.Offset(0, 1) = vOldVal
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, 3) = Time
.Offset(0, 4) = Date
End With
.Cells.Columns.AutoFit
'.Protect Password:="Secret"
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

Can I assign a formula to track when another cell is changed?
 

Thanks for the info. The link regarding the timestamp was very useful. I
created a COUNTIF formula to count the variable, within a given range, that
indicated a student was at a specified station. I used the
=IF(A1="","",IF(B1="",NOW(),B1)) formula in a seperate cell to display a time
when that variable changed. To help with tracking, I applied a conditioning
format to apply colors to cells to show who has been in a station the longest.

Thanks for your help.

"ryguy7272" wrote:

Would a Time Stamp work for you?
http://www.mcgimpsey.com/excel/timestamp.html

Or, this code can help you track changes:
Place under tab of Sheet2:
Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("a1:iv65536")
If Intersect(Target, r) Is Nothing Then Exit Sub
Application.EnableEvents = False
Cells(Target.Row, "Z").Value = Environ("username")
Application.EnableEvents = True
End Sub

Place under tab of Sheet1:
Dim vOldVal 'Must be at top of module
Private Sub Worksheet_Change(ByVal Target As Range)
Dim bBold As Boolean
Dim rArea As Range
Dim rCell As Range
For Each rArea In Target.Areas
For Each rCell In rArea
'your individual cell code here
Next rCell
Next rArea


If Target.Cells.Count 1 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:="Secret"
If .Range("A1") = vbNullString Then
.Range("A1:E1") = Array("CELL CHANGED", "OLD VALUE", _
"NEW VALUE", "TIME OF CHANGE", "DATE OF CHANGE")
End If


With .Cells(.Rows.Count, 1).End(xlUp)(2, 1)
.Value = Target.Address
.Offset(0, 1) = vOldVal
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, 3) = Time
.Offset(0, 4) = Date
End With
.Cells.Columns.AutoFit
'.Protect Password:="Secret"
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

HTH,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"richnlori1965" wrote:

I am using Excel to assign students to different assignments during a
practical examination. I understand I can use "track changes" to monitor
changes to an individual cells. However, I will have forty individuals
participating in twelve different activities. As each person completes a
station, a number is assigned to the corresponding cell. It would be too
difficult to check each cell for the last modification time for each person.

I would like to have a cell that monitors when a change occurs within a
range of cells. This would help me in monitoring how long it has been since a
student has completed an assignment. Any suggestions would be useful.