作业批改代码

作业批改代码

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列进行对比批改；

2、在sheet1！C1设置"对卷密码"并等于sheet2！F1的"对卷密码"，如果不输入密码,不能即时批改（考试或做作业用）,等他们上交给我时,我输入密码后才对全部进行批改；如果在C1输入密码后又可以即时进行批改（平时练习用）。


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
放进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