作業(yè)批改代碼
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
For Each Rng In Target
If Rng.Column = 4 And Rng.Row > 1 Then
If Rng = "" Then
Rng.Offset(, -3).ClearContents
Else
If UCase(Rng.Offset(, -1)) = "" Then
Rng.Offset(, -3).ClearContents
Else
' 正確答案列（此處怎樣改才能引用sheet2表呢）
' ↓
If UCase(Rng) = UCase(Rng.Offset(, 1)) Then
’If UCase(Rng) = UCase(Sheets(2).Range(Rng.Address).Offset(-2, -1)) Then
Rng.Offset(, -3) = "√"
Else
Rng.Offset(, -3) = "×"
End If
End If
End If
End If
Next
End Sub
1、把正確答案移到sheet2 C列進(jìn)行對(duì)比批改；
2、在sheet1！C1設(shè)置"對(duì)卷密碼"并等于sheet2！F1的"對(duì)卷密碼"，如果不輸入密碼,不能即時(shí)批改（考試或做作業(yè)用）,等他們上交給我時(shí),我輸入密碼后才對(duì)全部進(jìn)行批改；如果在C1輸入密碼后又可以即時(shí)進(jìn)行批改（平時(shí)練習(xí)用）。
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
放進(jìn)thiswordbook
另加VLOOKUP后：
'Private Sub Worksheet_Change(ByVal Target As Range)
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Rng As Range
For Each Rng In Target
If Rng.Column = 3 And Rng.Row > 1 Then
'If Rng.Offset(, -2) = "" Then End
If Rng = "" Then
Rng.Offset(, -1).ClearContents
Rng.Offset(, -2).ClearContents '注意
Rng.Offset(, 1).ClearContents
Rng.Offset(, 3).ClearContents
Else
Rng.Offset(, -2) = Application.WorksheetFunction.Max(Range("A1", Rng.Offset(-1, -2))) + 1 '注意
On Error Resume Next
If UCase(Rng) = Application.WorksheetFunction.VLookup(Rng.Offset(, -2), Range("I:L"), 3, False) Then
Rng.Offset(, -1) = "√"
Rng.Offset(, 1) = Application.WorksheetFunction.VLookup(Rng.Offset(, -2), Range("I:L"), 2, False)
Rng.Offset(, 3) = Application.WorksheetFunction.VLookup(Rng.Offset(, -2), Range("I:L"), 4, False)
Else
Rng.Offset(, -1) = "×"
Rng.Offset(, 1).ClearContents
Rng.Offset(, 3).ClearContents
Rng.Offset(1, 1) = Rng.Offset(1, 1) + 1
Rng.Offset(, 2).Interior.ColorIndex = 3
End If
End If
End If
Next
End Sub
A4==IF(AND(OR(ISERROR(MID(E4,1,1)*1)=FALSE,ISERROR(MID(E4,2,1)*1)=FALSE)=TRUE,MID(E5,1,1)="A")=TRUE,MAX(A$2:A3)+1,"")
隱藏
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
For Each Rng In Target
If Rng.Column = 14 And Rng.Row = 1 And Target.Value = "123" Then
Sheets("SHEET1").Visible = xlSheetVisible
End If
If Rng.Column = 14 And Rng.Row = 1 And Target.Value <> "123" Then
Sheets("SHEET1").Visible = xlSheetVeryHidden
End If
Next
End Sub
