I am happy enough writing macros but want some help to ensure I can
write the one I want!

I have a spreadsheet with pupil details in columns A-C, and then
currently in columns D-AA I hold marking details for student progress.

What I want to be able to do is each time a student's marks change, I
want to place the date in a helper column for the date of the most
recent change to the row. And what I really want is it to display
(maybe in another column) "Today" if it has changed today, "Week" if
it has changed in the last week, and "Month" if it has changed in the
last month. If over a month, then "Warning" needs to be displayed.

Any help gratefully received.

Noz
(Apologies for the earlier post in a different thread!)

Here is some code for you to try. I needs to go into the sheet so right click
the tab and select View Code. Paste the following...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
Application.EnableEvents = False
If Target.Count = 1 Then Cells(Target.Row, "AB").Value = Date

ErrorHandler:
Application.EnableEvents = True
End Sub

As for the Today, Week, Month you can do that with a formula that references
the date in column AB. You could also add conditional formatting to highlight
problem areas.
--
HTH...

Jim Thomlinson


"Nozza" wrote:

I am happy enough writing macros but want some help to ensure I can
write the one I want!

I have a spreadsheet with pupil details in columns A-C, and then
currently in columns D-AA I hold marking details for student progress.

What I want to be able to do is each time a student's marks change, I
want to place the date in a helper column for the date of the most
recent change to the row. And what I really want is it to display
(maybe in another column) "Today" if it has changed today, "Week" if
it has changed in the last week, and "Month" if it has changed in the
last month. If over a month, then "Warning" needs to be displayed.

Any help gratefully received.

Noz
(Apologies for the earlier post in a different thread!)

The following code may do what you are looking for. It assumes the data is
on a worksheet named Sheet1, but you can change this quite easily by
replacing the "Sheet1" with your actual worksheet name in the With statement
(4th line down). The code stores the date entered (always assumed to be
greater than the previous date; as written, there is no error checking to
enforce this, so you will have to add some if you think you will need it) in
Column AB on the row the date is entered in (you can hide this column if you
wish); it displays your "Today", "Week", "Month" and "Warning" messages in
Column AC; and it also displays the previous date in Column AD so you have a
reference to check the latest date against. Okay, that is it... just
copy/paste the code below my signature into your code window and you are
good to go.

Rick

Dim IgnoreThisEvent As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
Dim OldDate As Date
If IgnoreThisEvent Or Target.Count 1 Then Exit Sub
IgnoreThisEvent = True
With Worksheets("Sheet1")
If Not Intersect(Target, .Range("D:AA")) Is Nothing Then
If .Cells(Target.Row, "AB").Value = "" Then
.Cells(Target.Row, "AB").Value = Target.Value
OldDate = Target.Value
Else
OldDate = .Cells(Target.Row, "AB").Value
End If
If OldDate = Target.Value Then
.Cells(Target.Row, "AC").Value = "Today"
ElseIf Target - OldDate < 8 Then
.Cells(Target.Row, "AC").Value = "Week"
ElseIf Month(Target) - Month(OldDate) <= 1 And _
OldDate DateSerial(Year(Target), _
Month(Target) - 1, Day(Target)) Then
.Cells(Target.Row, "AC").Value = "Month"
Else
.Cells(Target.Row, "AC").Value = "Warning"
End If
.Cells(Target.Row, "AD").Value = "Previous Date: " & OldDate
.Cells(Target.Row, "AB").Value = Target.Value
End If
End With
IgnoreThisEvent = False
End Sub




"Nozza" wrote in message
...
I am happy enough writing macros but want some help to ensure I can
write the one I want!

I have a spreadsheet with pupil details in columns A-C, and then
currently in columns D-AA I hold marking details for student progress.

What I want to be able to do is each time a student's marks change, I
want to place the date in a helper column for the date of the most
recent change to the row. And what I really want is it to display
(maybe in another column) "Today" if it has changed today, "Week" if
it has changed in the last week, and "Month" if it has changed in the
last month. If over a month, then "Warning" needs to be displayed.

Any help gratefully received.

Noz
(Apologies for the earlier post in a different thread!)