Formatowanie warunkowe w Excel VBA
Możemy zastosować formatowanie warunkowe do komórki lub zakresu komórek w programie Excel. Format warunkowy to format, który jest stosowany tylko do komórek, które spełniają określone kryteria, np. Wartości powyżej określonej wartości, wartości dodatnie lub ujemne lub wartości z określoną formułą itp. To formatowanie warunkowe można również wykonać w programowaniu VBA w programie Excel przy użyciu „ Zbiór warunków formatu ” w makrze / procedurze.
Warunek formatu służy do reprezentowania formatu warunkowego, który można ustawić, wywołując metodę, która zwraca zmienną tego typu. Zawiera wszystkie formaty warunkowe dla jednego zakresu i może zawierać tylko trzy warunki formatowania.
FormatConditions.Add / Modify / Delete jest używany w języku VBA do dodawania / modyfikowania / usuwania obiektów FormatCondition do kolekcji. Każdy format jest reprezentowany przez obiekt FormatCondition. FormatConditions jest właściwością obiektu Range i Dodaj następujące parametry o poniższej składni:
FormatConditions.Add (typ, operator, formuła1, formuła2)
W składni formuły Add występują następujące argumenty:
- Typ: wymagany, określa, czy format warunkowy jest oparty na wartości obecnej w komórce czy wyrażeniu.
- Operator: opcjonalny, reprezentuje operator, który ma być używany z wartością, gdy „Typ” jest oparty na wartości komórki.
- Formuła1: opcjonalne, reprezentuje wartość lub wyrażenie powiązane z formatem warunkowym.
- Formuła2: opcjonalnie, przedstawia wartość lub wyrażenie powiązane z drugą częścią formatu warunkowego, gdy parametr „Operator” ma wartość „xlBetween” lub „xlNotBetween”.
FormatConditions.Modify ma również taką samą składnię jak FormatConditions.Add.
Poniżej znajduje się lista niektórych wartości / wyliczeń, które mogą być przyjmowane przez niektóre parametry „Dodaj” / „Modyfikuj”:
Przykłady formatowania warunkowego VBA
Poniżej znajdują się przykłady formatowania warunkowego w programie Excel VBA.
Przykład 1
Powiedzmy, że mamy plik Excela zawierający nazwiska i stopnie niektórych uczniów, i chcemy określić / wyróżnić znaki jako pogrubione i niebieskie, co jest większe niż 80, oraz jako pogrubione i czerwone w kolorze, które jest mniejsze niż 50. Zobaczmy dane zawarte w pliku:
Aby to osiągnąć, używamy FormatConditions. Dodaj funkcję jak poniżej:
- Przejdź do Developer -> Visual Basic Editor:
- Kliknij prawym przyciskiem myszy nazwę skoroszytu w panelu „Projekt VBAProject” -> „Wstaw” -> „Moduł”.
- Teraz napisz kod / procedurę w tym module:
Kod:
Formatowanie podrzędne () End Sub
- Zdefiniuj zmienną rng, warunek1, warunek2:
Kod:
Sub formatting () Dim rng As Range Warunek wygaszenia1 As FormatCondition, condition2 As FormatCondition End Sub
- Ustaw / napraw zakres, w którym formatowanie warunkowe ma być pożądane za pomocą funkcji VBA „Zakres”:
Kod:
Sub formatting () Dim rng As Range Wymień warunek1 As FormatCondition, condition2 As FormatCondition Ustaw rng = Range ("B2", "B11") End Sub
- Usuń / wyczyść wszelkie istniejące formatowanie warunkowe (jeśli istnieje) z zakresu, używając opcji „FormatConditions.Delete”:
Kod:
Sub formatting () Dim rng As Range Dim condition1 As FormatCondition, condition2 As FormatCondition Ustaw rng = Range ("B2", "B11") rng.FormatConditions.Delete End Sub
- Teraz zdefiniuj i ustaw kryteria dla każdego formatu warunkowego, używając opcji „FormatConditions.Add”:
Kod:
Sub formatting () Dim rng As Range Dim condition1 As FormatCondition, condition2 As FormatCondition Ustaw rng = Range ("B2", "B11") rng.FormatConditions.Delete Ustaw condition1 = rng.FormatConditions.Add (xlCellValue, xlGreater, "= 80 ") Ustaw condition2 = rng.FormatConditions.Add (xlCellValue, xlLess," = 50 ") End Sub
- Zdefiniuj i ustaw format, który ma być stosowany dla każdego warunku
Skopiuj i wklej ten kod do modułu klasy VBA.
Kod:
Sub formatting() 'Definining the variables: Dim rng As Range Dim condition1 As FormatCondition, condition2 As FormatCondition 'Fixing/Setting the range on which conditional formatting is to be desired Set rng = Range("B2", "B11") 'To delete/clear any existing conditional formatting from the range rng.FormatConditions.Delete 'Defining and setting the criteria for each conditional format Set condition1 = rng.FormatConditions.Add(xlCellValue, xlGreater, "=80") Set condition2 = rng.FormatConditions.Add(xlCellValue, xlLess, "=50") 'Defining and setting the format to be applied for each condition With condition1 .Font.Color = vbBlue .Font.Bold = True End With With condition2 .Font.Color = vbRed .Font.Bold = True End With End Sub
Teraz, gdy uruchamiamy ten kod za pomocą klawisza F5 lub ręcznie, widzimy, że znaki, które są mniejsze niż 50, są wyróżniane pogrubioną i czerwoną czcionką, podczas gdy te, które są większe niż 80, są wyróżniane pogrubioną i niebieską w następujący sposób:
Przykład nr 2
Powiedzmy, że w powyższym przykładzie mamy również inną kolumnę, w której stwierdza się, że uczeń jest „Topper”, jeśli uzyska więcej niż 80 punktów, w przeciwnym razie wpisano wynik zaliczony / niezaliczony. Teraz chcemy wyróżnić wartości określone jako „Topper” jako pogrubioną i niebieską. Zobaczmy dane zawarte w pliku:
W takim przypadku kod / procedura działałaby w następujący sposób:
Kod:
Sub TextFormatting () End Sub
Zdefiniuj i ustaw format, który ma być stosowany dla każdego warunku
Kod:
Sub TextFormatting () With Range ("c2: c11"). FormatConditions.Add (xlTextString, TextOperator: = xlContains, String: = "topper") With .Font .Bold = True .Color = vbBlue End With End With End Sub
Widzimy w powyższym kodzie, który chcemy przetestować, jeśli zakres: „C2: C11” zawiera ciąg: „Topper”, więc parametr: „Onamestor” z „Format.Add” przyjmuje wyliczenie: „Xcontains” do przetestuj ten warunek w ustalonym zakresie (tj. C2: C11), a następnie wykonaj wymagane formatowanie warunkowe (zmiany czcionki) w tym zakresie.
Teraz, gdy uruchamiamy ten kod ręcznie lub naciskając klawisz F5, widzimy, że wartości komórek z „Topper” są podświetlane na niebiesko i pogrubione:
Below are some other instances/criteria that can be used to test and thus apply VBA conditional formatting on:
- Format by Time Period
- Average condition
- Colour Scale condition
- IconSet condition
- Databar condition
- Unique Values
- Duplicate Values
- Top10 values
- Percentile Condition
- Blanks Condition, etc.
With different conditions to be tested, different values/enumeration are taken by parameters of ‘Add.’
Things to Remember About VBA Conditional Formatting
- ‘Add’ method with ‘FormatConditions’ is used to create a new conditional format, ’Delete’ method to delete any conditional format, and ‘Modify’ method to alter any existing conditional format.
- The ‘Add’ method with ‘FormatConditions Collection’ fails if more than three conditional formats are created for a single range.
- Aby zastosować więcej niż trzy formaty warunkowe do zakresu za pomocą metody „Dodaj”, możemy użyć opcji „Jeśli” lub „Wybierz wielkość liter”.
- Jeśli metoda „Add” ma parametr „Type” jako: „xlExpression”, to parametr „Operator” jest ignorowany.
- Parametry: „Formuła1” i „Formuła2” w metodzie „Dodaj” mogą być odwołaniem do komórki, wartością stałą, wartością ciągu, a nawet formułą.
- Parametr: „Formuła2” jest używany tylko wtedy, gdy parametr: „Operator” ma wartość „xlBetween” lub „xlNotBetween”, w przeciwnym razie jest ignorowany.
- Aby usunąć całe formatowanie warunkowe z dowolnego arkusza, możemy użyć metody „Usuń” w następujący sposób:
Cells.FormatConditions.Delete
