highlighting positive or negative change

[Dave]

Hi

I have a worksheet which calculates a running average of student test
scores. An adjacent cell then has a vlookup function that displays a
grade based on the average test score and the level of the paper. The
grades are a mixture on numbers and letters, thus;

6a
6b
6c
5a
5b
5c
4a
4b

I have to provide "encouraging feedback" to the students after every
paper and report on whether their grade has gone up or down. My
problem is that the grade field changes instantly I type in the latest
score. Is there any way that I could flag up a change of grade, and
whether it was an improvement or not,perhaps by changing the colour
formatting of the grade displayed.

Any help would be greatly appreciated,

Dave

 

[Harald Staff]

Hi Dave

Part one is to make a reasonable mumber from your grades. Since A < B to a
computer, and probably not to your grade system, you need some numeric value
like

=VALUE(LEFT(A1,1))+(68-CODE(MID(UPPER(A1),2,1)))/10

This will return 5.3 from 5a and 5.1 from 5c, and you can compare them with
simple < = > operators.

The rest is not clear to me. You say
"the grade field changes instantly I type in the latest score"
Please explain exactly how and where the changes appear.

I hope you are not asking for "keep the old formula result somewhere before
calculating the new one". That is not impossible, but requires either a
manual copy-paste or a macro used with dicipline. A formula cell does not
have a history of results, so if history is important then maybe another
design is required.

HTH. Best wishes Harald

 

[Bob Phillips]

Dave,

Here is one way, but it requires some setup.

I am assuming that the scores are in column A and the grades in column B,
and that the lookup table is on the same sheet.

First insert a column in-between (which we will use to store previous
grades).
Then, add a workbook name to the scores, named 'scores' (without the quotes
Add a workbook name to the (calculated) grades called 'grades'
Add a workbook name to the grades in the lookup table, called 'gradings'
Then add this worksheet event code

Option Explicit

Private Sub Worksheet_Activate()
Dim cell As Range
For Each cell In Me.Range("grades")
cell.Offset(0, -1).Value = cell.Value
Next cell
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("scores")) Is Nothing Then
On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
Select Case CalcGradeShift(.Offset(0, 2).Value, .Offset(0,
1).Value)
Case Is < -1: .Offset(0, 2).Interior.ColorIndex = 3 'red
Case Is < 0: .Offset(0, 2).Interior.ColorIndex = 46 'orange
Case Is > 0: .Offset(0, 2).Interior.ColorIndex = 10 'green
End Select
.Offset(0, 1).Value = .Offset(0, 2).Value
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Private Function CalcGradeShift(val, oldVal) As Long
Dim aryGrades
Dim cell As Range
Dim i As Long

i = 1
For Each cell In Me.Range("gradings")
If cell.Value = val Then
CalcGradeShift = i
Exit For
End If
i = i + 1
Next cell
i = 1
For Each cell In Me.Range("gradings")
If cell.Value = oldVal Then
CalcGradeShift = CalcGradeShift - i
Exit For
End If
i = i + 1
Next cell
End Function


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




RP
(remove nothere from the email address if mailing direct)