之所以會想著手這篇文章，是因為這算是我寫VBA macro的初體驗。過去雖然有使用過Excel VBA的經驗，但基本上都是透過錄製來完成巨集的編輯，而這次則是我自己以撰寫程式的方式來完成Macro。姑且不論寫的codes真的非常醜，但只要能跑的Macro就是好Macro（笑）。
在開始說明我做了什麼前，我想先介紹這專案的背景：
（使用者故事卡片-A）
我作為一個產品營運專員，我希望可以更快速地讓我能整理客戶報表上的資料，讓其一目瞭然。
（使用者故事卡片-B, 完整版）
作為一個產品營運專員，我時常需要檢查客戶報表是否有錯（錯誤的值、錯誤的格式etc），然而報表上的大量資料讓我總得花費許多時間在一一檢查表格，我希望能找到更有效率的方式來整理數據。
在撰寫這VBA前，我的作法是每次檢視客戶的檔案時用Excel的格式化條件、Sort/Filter來整理資料，但實際上我要看的項目是固定的，而每次都要重新設定格式化條件實在有些浪費時間。另個困難是我要檢查多項條件，我想要同時檢查該格數字不等於A, 不等於B且不等於C — — 基本的格式化條件卻無法以聯集方式篩選A且B且C。
格式化條件聯集缺陷其實是Excel格式化條件中相當有趣的設定。
在Excel的格式化條件設定中包含了「優先順序」設定。舉例而言，在A行中，若我的第一個格式化設定條件為大於1的欄位必須標為紅色，而第二個格式化條件則是大於2的欄位必須標為綠色。那麼這兩個條件在Excel系統內會判定為衝突，因而只會應用優先順序較高的條件。
以下兩張截圖應該就可以很清楚看出差別為何。在截圖1中，因為大於1設定為紅色條件的優先順序為首位，因而條件2就自動被捨棄了。而在截圖2中，我將條件2的順序設定為首位，所以他會先將大於2的欄位標為綠色後再檢查條件1。
因為在多項條件下需要設定優先順序，與我本身想要達成的效果有些微出入，因而最終我決定以VBA的方式來完成目標。
請參考下方的截圖，示範用的Spreadsheet提供於文章最末。
我希望達成的目標是：水果欄位底下，除了蘋果與橘子外的所有東西都應該標示為「錯誤」（紅色）。
讓我們先從一個條件設定開始：
蘋果之外的任何表格都應標示為紅色。
說明版再繼續往下之前，先提供大家一個Hack Tip, 若你像我一樣需要跑很多次VBA測試的話，你可能也對每次要手動清掉格式化的格子感到厭煩。你可以在 Range(“B2”).Select 與 Range(Selection, Selection.End(xlDown)).Select 中間加上一條清掉格式的Code：
Range(Selection, Selection.End(xlDown)).FormatConditions.Delete
這樣你就不用再手動清除格式化的格子了，因為每跑一次新的VBA, 它都會先清除完格子後再幫你檢查下面的條件內容。
進階到兩個條件：蘋果與橘子之外的任何表格都應該標示為紅色。
從一個條件到兩個條件的階段難度上升許多，這大概也是為什麼Stack Overflow 上大多數人都跟我一樣瘋狂搜尋”How to set up multiple conditions in conditional formatting using VBA” 當然，若你有更簡單的寫法，非常歡迎你在文章之下留言讓我知道！分享讓學習變得更有意義！
- 當進階到兩個條件時，Operator在當中所扮演的角色會對VBA的邏輯有很大的影響。在此我嘗試使用 Or / And 的公式來連結兩項條件。
OR的功能如下：
Function (OR) =OR(Logical_test1, Logical_test2)
只要Logical_test1為真 Or Logical_test2為真，則顯示為TRUE
反之，若Logical_test1 AND Logical_test2 皆不為真，則顯示為FALSEAND的功能如下：
Function (AND) =AND(Logical_test1, Logical_test2)
只要Logical_test1為真 AND Logical_test2為真，則顯示為TRUE
反之，若 Logical_test1 OR Logical_test2 （有一項）不為真，則顯示為FALSE
- 若你想使用AND（且）的公式，則不符合（格子=蘋果 and 格子=橘子）的條件符合就會被標為紅色，但很明顯的不會有任何一個格子同時等於蘋果又等於橘子。所以AND的用法在此不能使用。換個角度想，那使用OR會有什麼結果呢？公式改為不符合（格子=蘋果 or 格子=橘子）的條件符合就會被標為紅色，確實是我們要的結果，但此時要設想另一個OR公式上的限制：
我們的條件格式化拿xCellValue（格子內的值）以 NotEqual （不等同於）來檢驗是否符合Formula的結果，但OR公式所吐出來的值是TRUE/ FALSE，所以除非格子內的值本身就是TRUE / FALSE，不然永遠都不會達成這項條件。白話文說明是，拿APPEL這格子來比較OR功能跑出來後的TRUE/FALSE結果永遠都不符合。
於是我需要再多包一個功能讓它能把TRUE的值轉換為原本的APPLE或是ORANGE, 這就用最簡單的功能IF來完成。
Function (IF) =IF(Logical_test,Value_if_true, Value_if_false)
若Logical_test為真，則顯示Value_if_true的值，若為假，則顯示Value_if_false的值
於是我將公式改寫為 IF(OR(B2=""APPLE"",B2=""ORANGE""), B2,FALSE)
- 先跑OR的功能：若 B2=APPLE 或 B2=ORANGE 任何一項條件完成則顯示TRUE
- 若(1)顯示為TRUE的話，那麼保留原本那一格的內容B2
- 若(1)顯示為FALSE的話，那麼顯示為FALSE
因此，條件式格式化所比較的就會是xlCellValue（被選擇的任一格）與原本格子內容（如果符合等於蘋果或是等於橘子的條件）或FALSE這個值（如果不符合條件）是否相同。所以若原本格子就是ORANGE或是APPLE的話就會符合這條件。但別忘了，我們所設定的目標是「排除」（NotEqual）- 因而最終呈現結果會是「不等於橘子與不等於蘋果的水果被標示出來」。
同理，三個條件的話就繼續往後加內容了。
例如： =IF(OR(B2=""APPLE"",B2=""ORANGE"",B2=""BANANA""), B2,FALSE)
說明版完成版截圖
在此提供大家另外一種寫法，也這是我原本最開始的寫法。實際上條件式格式化的功能等同於最基本的IF功能，所以我們可以將條件格式化理解為IF, ELSE, THEN的段落。思考脈絡為：
IF B2 不等於 蘋果
OR B2 不等於 橘子
THEN Nothing
ELSE 把格子變為紅色==實際寫法（不額外說明，因為該寫法較好理解）==Sub find_No_Apple_orange()
Range("B2:B").Select
Range(Selection, Selection.End(xlDown)).Select
If B2 = "APPLE" Or B2 = "ORANGE" Then
Else
Selection.Interior.Color = RGB(255, 199, 206) '只是改成用RGB色碼標示顏色而已
End IfEnd Sub
這與Conditional formatting的寫法有何差別呢？主要在於Excel本身功能上的差異:
- Conditioanl Formatting是一個被內嵌在格子內的公式，隨著你條件符合或不符合，它會自動檢查更改顏色。
- 而IF ElSE寫法則是直接選取符合條件的格子，將格子上色。但即便你之後改變了格子的內容，上色的格子會持續維持上色的狀態。
另外一個差異是，若你是用VBA來跑條件格式化的話，所有你的條件會顯示在「條件式格式化」功能下「管理條件」中。你可以改變不同條件想要顯示的顏色，並且快速設定。綜觀以上的比較，我最終選擇以Conditional_formatting的寫法來完成我這次的專案。
下一篇文章會繼續精煉這個VBA，將它改寫為「依照條件可自動搜尋格式化應用範圍」的功能。Special thanks to Luis, and huge credit to my roommate Huli for helping me to finalize the VBA codes.
- SpreadSheet template:
https://drive.google.com/file/d/0B0mVzsAoiBq0YXFRTi1ZYWZhcjg/view?usp=sharing - Reference: VBA conditional Formatting official documentation
https://msdn.microsoft.com/en-us/library/office/ff837832.aspx - Reference: How to add multiple conditional formatting in VBA
https://stackoverflow.com/questions/41011532/how-to-add-multiple-conditional-formatting-in-vba-coding-excel - Reference: How to apply the conditional formatting to multiple columns using macro
https://stackoverflow.com/questions/10292921/how-to-apply-the-conditional-formatting-to-multiple-columns-using-excel-macro
