標題: 簡化程式碼 [打印本頁]

---

作者: tinching    時間: 2011-12-23 20:39     標題: 簡化程式碼

下列程式碼有可能簡化嗎？謝謝各位大大！
IF [CA13] = [CZ15] THEN
[Q4:R4].Font.ColorIndex = 3
End If
IF [CB13] = [CZ15] THEN
[S4:T4].Font.ColorIndex = 3
End If
IF [CC13] = [CZ15] THEN
[U4:V4].Font.ColorIndex = 3
End If
IF [CD13] = [CZ15] THEN
[W4:X4].Font.ColorIndex = 3
End If
IF [CE13] = [CZ15] THEN
[Y4:Z4].Font.ColorIndex = 3
End If
IF [CF13] = [CZ15] THEN
[AA4:AB4].Font.ColorIndex = 3
End If
IF [CG13] = [CZ15] THEN
[AC4:AD4].Font.ColorIndex = 3
End If

IF [CA14] = [CZ15] THEN
[Q5:R5].Font.ColorIndex = 3
End If
IF [CB14] = [CZ15] THEN
[S5:T5].Font.ColorIndex = 3
End If
IF [CC14] = [CZ15] THEN
[U5:V5].Font.ColorIndex = 3
End If
IF [CD14] = [CZ15] THEN
[W5:X5].Font.ColorIndex = 3
End If
IF [CE14] = [CZ15] THEN
[Y5:Z5].Font.ColorIndex = 3
End If
IF [CF14] = [CZ15] THEN
[AA5:AB5].Font.ColorIndex = 3
End If
IF [CG14] = [CZ15] THEN
[AC5:AD5].Font.ColorIndex = 3
End If

IF [CA15] = [CZ15] THEN
[Q6:R6].Font.ColorIndex = 3
End If
IF [CB15] = [CZ15] THEN
[S6:T6].Font.ColorIndex = 3
End If
IF [CC15] = [CZ15] THEN
[U6:V6].Font.ColorIndex = 3
End If
IF [CD15] = [CZ15] THEN
[W6:X6].Font.ColorIndex = 3
End If
IF [CE15] = [CZ15] THEN
[Y6:Z6].Font.ColorIndex = 3
End If
IF [CF15] = [CZ15] THEN
[AA6:AB6].Font.ColorIndex = 3
End If
IF [CG15] = [CZ15] THEN
[AC6:AD6].Font.ColorIndex = 3
End If

IF [CA16] = [CZ15] THEN
[Q7:R7].Font.ColorIndex = 3
End If
IF [CB16] = [CZ15] THEN
[S7:T7].Font.ColorIndex = 3
End If
IF [CC16] = [CZ15] THEN
[U7:V7].Font.ColorIndex = 3
End If
IF [CD16] = [CZ15] THEN
[W7:X7].Font.ColorIndex = 3
End If
IF [CE16] = [CZ15] THEN
[Y7:Z7].Font.ColorIndex = 3
End If
IF [CF16] = [CZ15] THEN
[AA7:AB7].Font.ColorIndex = 3
End If
IF [CG16] = [CZ15] THEN
[AC7:AD7].Font.ColorIndex = 3
End If

IF [CA17] = [CZ15] THEN
[Q8:R8].Font.ColorIndex = 3
End If
IF [CB17] = [CZ15] THEN
[S8:T8].Font.ColorIndex = 3
End If
IF [CC17] = [CZ15] THEN
[U8:V8].Font.ColorIndex = 3
End If
IF [CD17] = [CZ15] THEN
[W8:X8].Font.ColorIndex = 3
End If
IF [CE17] = [CZ15] THEN
[Y8:Z8].Font.ColorIndex = 3
End If
IF [CF17] = [CZ15] THEN
[AA8:AB8].Font.ColorIndex = 3
End If
IF [CG17] = [CZ15] THEN
[AC8:AD8].Font.ColorIndex = 3
End If

IF [CA18] = [CZ15] THEN
[Q9:R9].Font.ColorIndex = 3
End If
IF [CB18] = [CZ15] THEN
[S9:T9].Font.ColorIndex = 3
End If
IF [CC18] = [CZ15] THEN
[U9:V9].Font.ColorIndex = 3
End If
IF [CD18] = [CZ15] THEN
[W9:X9].Font.ColorIndex = 3
End If
IF [CE18] = [CZ15] THEN
[Y9:Z9].Font.ColorIndex = 3
End If
IF [CF18] = [CZ15] THEN
[AA9:AB9].Font.ColorIndex = 3
End If
IF [CG18] = [CZ15] THEN
[AC9:AD9].Font.ColorIndex = 3
End If

---

作者: register313    時間: 2011-12-23 22:24

回復 1# tinching
初學者vba

1. Sub xx()
   
2. 
   
3. For C = 13 To 18
   
4.   J = 17
   
5.   For R = 79 To 87
   
6.     If Cells(C, R) = [CZ15] Then
   
7.          Range(Cells(C - 9, J), Cells(C - 9, J + 1)).Font.ColorIndex = 3
   
8.        Else
   
9.          Range(Cells(C - 9, J), Cells(C - 9, J + 1)).Font.ColorIndex = 0
   
10.     End If
    
11.     J = J + 2
    
12.   Next R
    
13. Next C
    
14. 
    
15. End Sub

複製代碼

---

作者: GBKEE    時間: 2011-12-24 16:40

本帖最後由 GBKEE 於 2011-12-24 16:52 編輯

回復 1# tinching

1. Sub Ex()
   
2.     Dim E As Range, i As Integer
   
3.     [Q4:AD9].Font.ColorIndex =xlAutomatic
   
4.     For Each E In [CA13:CG18]
   
5.         If E.Column = 79 Then i = 17 Else i = i + 2
   
6.         If E = [z15] Then Cells(E.Row - 9, i).Resize(, 2).Font.ColorIndex = 3
   
7.     Next
   
8. End Sub

複製代碼

---

歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)