VB Code:
Sub checkrecord()
Dim s As Sheet4, f As Sheet1, newrecord As Boolean
Set s = Sheet4
Set f = Sheet1
n = f.Name
's.Activate
For i = 2 To s.UsedRange.Rows.Count
If s.Cells(i, 3) = f.Cells(3, 2) And s.Cells(i, 4) = f.Cells(4, 2) And s.Cells(i, 5) = f.Cells(6, 2) Then
a = f.Cells(6, 2)
Select Case f.Cells(6, 2)
Case "100m", "200m", "400m", "800m", "1500m", "Relay"
If f.Cells(8, 2) < s.Cells(i, 6) Then newrecord = True
Case "Shot Putt", "Long Jump", "Triple Jump", "High Jump", "Discus", "Javelin"
If f.Cells(8, 2) > s.Cells(i, 6) Then newrecord = True
Case Else
MsgBox "This event not in record list"
End Select
If newrecord Then 'compare time/distance, not sure if high or low is better
s.Cells(i, 2) = f.Cells(2, 2) ' update record if better
s.Cells(i, 6) = f.Cells(8, 2)
s.Cells(i, 7) = Year(Now)
End If
Exit For
End If
Next
'for i = 1 to
End Sub