请将以下程序放到工作表的代码部分(确保已经启用宏)
Dim tRange As String '保存上一次所访问的范围
Private Sub Worksheet_Activate() '当从别的工作表切换到本工作表时出现提示
MsgBox ("在R3中输入学号并按回车键后会自动定位到所找学生行,输完内容后按右箭头回到R3!!")
MsgBox "已经使用:(" & Sheet1.UsedRange.Rows.Count & "行," & Sheet1.UsedRange.Columns.Count & "列)"
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Lie '定义你要输入分数的列,A:1，B:2，C:3，D:4，E:5……
Lie = 8 '假定默认为在第8列即H列中输入分数
'以下判断是否在R3中输入待查找学号
If (Target.Row = 3) And (Target.Column = 18) And (ActiveCell.Value <> "") Then
For Each c In [A4:A120]
If Trim(c.Value) Like ("*" & Trim(ActiveCell.Value)) Then
Range(c.Address & ":" & Chr(Asc("A") - 1 + Lie) & c.Row).Select
tRange = Selection.Address '将选定的范围保存到临时变量中
Selection.Interior.ColorIndex = 33 '选中后背景颜色的变化
Selection.Interior.Pattern = xlSolid
c.Offset(0, Lie - 1).Select '选定区域中最后一单元格
Exit For '已经找到目标学号退出继续查找程序
End If
Next
ElseIf (Target.Column = Lie) And (ActiveCell.Value <> "") Then
If tRange <> "" Then
Range(tRange).Select
Selection.Interior.ColorIndex = xlNone
End If
Range("R3").Select
End If
End Sub
