[發問] 自動判定成績合格及不合格，但不是依分數判定

qq78787

各位高手大大

在EXCEL要如何判定成績自己秀出合格及不合格，但並不是依照分數，而是依照如果一個科目中有一個成績是紅色的就自己秀出不合格
附上圖檔及excel擋
感謝

下載 (26.86 KB)

2015-2-7 09:41

如何判定.zip (9.86 KB)

下載次數: 8

2015-2-7 09:43

rouber590324

如下

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
For M = 4 To 9
If Sheet1.Cells(M, 6).Font.ColorIndex = 3 Or Sheet1.Cells(M, 8).Font.ColorIndex = 3 Or Sheet1.Cells(M, 10).Font.ColorIndex = 3 Or Sheet1.Cells(M, 12).Font.ColorIndex = 3 Then
  Sheet1.Cells(M, 14) = "不合格"
  Else
  Sheet1.Cells(M, 14) = "合格"
End If
Next
End Sub

rouber590324

+不合格自動秀紅色

Sub AA()
For M = 4 To 9
If Sheet1.Cells(M, 6).Font.ColorIndex = 3 Or Sheet1.Cells(M, 8).Font.ColorIndex = 3 Or Sheet1.Cells(M, 10).Font.ColorIndex = 3 Or Sheet1.Cells(M, 12).Font.ColorIndex = 3 Then
  Sheet1.Cells(M, 14) = "不合格"
  Sheet1.Cells(M, 14).Font.ColorIndex = 3
  Else
  Sheet1.Cells(M, 14) = "合格"
End If
Next

End Sub

fantersy

回復 1# qq78787


如何判定.rar (11.44 KB)

下載次數: 5

2015-2-9 11:53

  基本方式
N4輸入
=IF(OR(F4="",H4="",J4="",L4=""),"",IF(OR(F4<60,H4<60,J4<60,L4<60),"不合格","合格"))
或
N4輸入
=IF(OR(F4="",H4="",J4="",L4=""),"",IF(SUMPRODUCT(N(F4:M4<60))>4,"不合格","合格"))
N4輸入
=IF(OR(F4="",H4="",J4="",L4=""),"",IF(COUNTIF(F4:M4,">=60")=4,"合格","不合格"))
顏色部份使用
設定格式化的條件去設定
不合格 紅色
低於60分紅色

qq78787

回復 3# rouber590324

rouber590324  大大你好

請問我複製這段上去 他為何會錯?

下載 (74.01 KB)

2015-2-9 13:09

再請你指教了 感謝~

rouber590324

DEAR SIR
1將如下覆製到你有資料之SHEET1 "工作表中"就OK啦
  不要置於MODULE


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
For M = 4 To 9
If Sheet1.Cells(M, 6).Font.ColorIndex = 3 Or Sheet1.Cells(M, 8).Font.ColorIndex = 3 Or Sheet1.Cells(M, 10).Font.ColorIndex = 3 Or Sheet1.Cells(M, 12).Font.ColorIndex = 3 Then
  Sheet1.Cells(M, 14) = "不合格"
  Sheet1.Cells(M, 14).Font.ColorIndex = 3
  Else
  Sheet1.Cells(M, 14) = "合格"
  Sheet1.Cells(M, 14).Font.ColorIndex = 1
End If
Next
End Sub

qq78787

回復 6# rouber590324

rouber590324  大大你好
對不起 還是有錯??是不是我又複製錯地方了?
如果方便可以貼個圖讓我學習~~謝謝

下載 (186.51 KB)

2015-2-9 13:42

rouber590324

我公司檔傳檔案與圖片功能-不然早上傳ZIP檔給你拉
我看是板本問題
我將 SHEET1 改 工作表1 再試試
因它找不到 SHEET1 故秀出 BUG

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
For M = 4 To 9
If 工作表1.Cells(M, 6).Font.ColorIndex = 3 Or 工作表1.Cells(M, 8).Font.ColorIndex = 3 Or 工作表1.Cells(M, 10).Font.ColorIndex = 3 Or 工作表1.Cells(M, 12).Font.ColorIndex = 3 Then
  工作表1.Cells(M, 14) = "不合格"
  工作表1.Cells(M, 14).Font.ColorIndex = 3
  Else
  工作表1.Cells(M, 14) = "合格"
  工作表1.Cells(M, 14).Font.ColorIndex = 1
End If
Next
End Sub

qq78787

回復 8# rouber590324

有了有了
謝謝你~~ :)