Private Sub CommandButton1_Click() For i = 3 To 34 If Cells(i, 20) = 3 Then Cells(i, 20).Font.ColorIndex = 3 Else Cells(i, 20).Font.ColorIndex = 1 End If Next i End Sub
Private Sub CommandButton1_Click() If UserForm5.TextBox1.Value = “” Then MsgBox “成績表を表示する週が入力されていません” Else ActiveSheet.Unprotect Range(“BJ3”).Value = UserForm5.TextBox1.Value Application.ScreenUpdating = False ActiveSheet.Protect Sheets(“RESULT”).Select ActiveSheet.Unprotect Range(“B3:V34”).Sort Key1:=Range(“U2”), Order1:=xlDescending, Key2:=Range(“K2”), Order2:=xlDescending Application.ScreenUpdating = True Range(“A1”).Select Unload UserForm5 Dim LastRow As Long Dim i As Long LastRow = Cells(Rows.Count, 1).End(xlUp).Row For i = 1 To LastRow If Cells(i, 26) = 0 Then Rows(i).Hidden = True Next i Select Case Worksheets(“RESULT”).Range(“AA1”).Value Case Is = 18 Range(“3:20”).RowHeight = 40 Range(“A3:V34”).Font.Size = 22 Case Is = 19 Range(“3:21”).RowHeight = 40 Range(“A3:V34”).Font.Size = 22 Case Is = 20 Range(“3:22”).RowHeight = 40 Range(“A3:V34”).Font.Size = 22 Case Is = 21 Range(“3:23”).RowHeight = 40 Range(“A3:V34”).Font.Size = 22 Case Is = 22 Range(“3:24”).RowHeight = 40 Range(“A3:V34”).Font.Size = 22 Case Is = 23 Range(“3:25”).RowHeight = 40 Range(“A3:V34”).Font.Size = 22 Case Is = 24 Range(“3:26”).RowHeight = 40 Range(“A3:V34”).Font.Size = 22 Case Is = 25 Range(“3:27”).RowHeight = 38 Range(“A3:V34”).Font.Size = 22 Case Is = 26 Range(“3:28”).RowHeight = 36 Range(“A3:V34”).Font.Size = 22 Case Is = 27 Range(“3:29”).RowHeight = 35 Range(“A3:V34”).Font.Size = 16 Case Is = 28 Range(“3:30”).RowHeight = 33 Range(“A3:V34”).Font.Size = 16 Case Is = 29 Range(“3:31”).RowHeight = 32 Range(“A3:V34”).Font.Size = 15 Case Is = 30 Range(“3:32”).RowHeight = 31 Range(“A3:V34”).Font.Size = 15 Case Is = 31 Range(“3:33”).RowHeight = 30 Range(“A3:V34”).Font.Size = 15 Case Is = 32 Range(“3:34”).RowHeight = 29 Range(“A3:V34”).Font.Size = 15 End Select Range(“A1:V34”).Select ActiveWindow.Zoom = True For i = 3 To 34 If Cells(i, 20) = 3 Then Cells(i, 20).Font.ColorIndex = 3 Else Cells(i, 20).Font.ColorIndex = 1 End If Next i ActiveSheet.Protect End If End Sub
