Snasui Excel Forum

Code: Select all

Sub StandardTime_Button1_Click()
' ปุ่มคำนวนค่าเวลาปกติ
Dim i As Long
Dim xbar(300), rating As Double
Dim normal(300), normalmax As Double

Dim orange As Range
Dim oformat As FormatCondition


For i = 1 To Val(Range("B2"))
   If ThisWorkbook.Sheets("Standard Time").Cells(2, i + 5) <> "" Then
      'ส่วนการคำนวณ
      xbar(i) = Application.WorksheetFunction.Average(Range(Cells(3, i + 5), Cells(3, i + 5).End(xlDown)))
      rating = Val(Range("B3")) / 100
      normal(i) = rating * xbar(i)
     
       normalmax = Application.WorksheetFunction.Max(normal)
   Else
      Exit Sub
   End If
'   ส่วนที่คัดกรองค่า
   Set orange = Range(Cells(3, i + 5), Cells(3, i + 5).End(xlDown))
   Set oformat = orange.FormatConditions.Add(xlAboveAverageCondition, xlFormula)
   oformat.Font.Color = RGB(250, 0, 0)
Next i
ThisWorkbook.Sheets("Standard Time").Range("B4") = normalmax
End Sub

Code: Select all

'Other code
Set orange = Range(Cells(3, i + 5), Cells(3, i + 5).End(xlDown))
Set oformat = orange.FormatConditions.Add(xlCellValue, xlNotEqual, xbar(i))
oformat.Font.Color = RGB(250, 0, 0)
'Other code

Code: Select all

   
   Set orange = Range(Cells(3, i + 5), Cells(3, i + 5).End(xlDown))
   Set oformat = orange.FormatConditions.Add(xlCellValue, xlBetween, xbar(i))
   oformat.Font.Color = RGB(250, 0, 0)