Snasui Excel Forum

Code: Select all

Sub ปรับรูปแบบตาราง()
Application.ScreenUpdating = False
Call Module5.UnProtectAll2
    Sheets(Array("ไทย1", "คณิต1", "วิทย์1", "สังคม1", "ประวัติ1", "หน้าที่1", "ไทย2", "คณิต2", "วิทย์2", "สังคม2", "ประวัติ2", "หน้าที่2")).Select
    Sheets("ไทย1").Activate
    '********************* ตีตาราง  ปรับฟอนต์  จัดกึ่งกลาง
Range("E7:X57, AB8:AC57, AJ8:AK57").Select
        Selection.FormatConditions.Delete
Selection.Font.Name = "AngsanaUPC"
Selection.Font.Size = 14
Selection.Font.Bold = False
With Selection
.HorizontalAlignment = xlCenter
Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
Selection.Borders(xlEdgeTop).LineStyle = xlContinuous
Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
Selection.Borders(xlEdgeRight).LineStyle = xlContinuous
Selection.Borders(xlInsideVertical).LineStyle = xlContinuous
Selection.Borders(xlInsideHorizontal).LineStyle = xlContinuous
'*********************  ใส่เช็ค คะแนนต่ำกว่าครึ่งแดง และคะแนนเกินพื้นแดง
    Application.Goto Reference:=Range("E8:E57").Resize(, 33)                 
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
        Formula1:="=E$7/2"                                           
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = True
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=E$7"                                                    
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16711681
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = True
Application.ScreenUpdating = True
End With
    Sheets("หน้าแรก").Select
    Range("E57").Select
    Range("E11").Select
End Sub

Code: Select all

Sub ปรับตาราง()
Application.ScreenUpdating = False
    Sheets(Array("2", "3", "4", "5", "6", "8", "9", "10", "11", "12")).Select
    Sheets("2").Activate
    '********************* ???????  ?????????  ???????????
Range("E7:X57, AB8:AC57, AJ8:AK57").Select
        Selection.FormatConditions.Delete
Selection.Font.Name = "AngsanaUPC"
Selection.Font.Size = 14
Selection.Font.Bold = False
With Selection
.HorizontalAlignment = xlCenter
Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
Selection.Borders(xlEdgeTop).LineStyle = xlContinuous
Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
Selection.Borders(xlEdgeRight).LineStyle = xlContinuous
Selection.Borders(xlInsideVertical).LineStyle = xlContinuous
Selection.Borders(xlInsideHorizontal).LineStyle = xlContinuous
'*********************  ??????? ???????????????????? ???????????????????
    Application.Goto Reference:=Range("E8:E57").Resize(, 33)
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
        Formula1:="=E$7/2"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = True
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=E$7"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16711681
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = True
Application.ScreenUpdating = True
End With
    Sheets("2").Select
    Range("E7").Select
End Sub

Code: Select all

'Other code
'*********************  ??????? ???????????????????? ???????????????????
Sheets(1).Select
For Each sh In Sheets(Array("2", "3", "4", "5", "6", "8", "9", "10", "11", "12"))
    Application.Goto Reference:=sh.Range("E8:E57").Resize(, 33)
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
        Formula1:="=E$7/2"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = True
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=E$7"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16711681
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = True
Next sh
Application.ScreenUpdating = True
'Other code