Условно форматиране на VBA - Приложете условен формат с помощта на VBA Excel

Условно форматиране в Excel VBA

Можем да приложим условно форматиране към клетка или диапазон от клетки в Excel. Условен формат е формат, който се прилага само към клетки, които отговарят на определени критерии, да речем стойности над определена стойност, положителни или отрицателни стойности или стойности с определена формула и т.н. Това условно форматиране може да се направи и в програмирането на Excel VBA, като се използва „ Събиране на условия за формат “ в макроса / процедурата.

Format Condition се използва за представяне на условен формат, който може да бъде зададен чрез извикване на метод, който връща променлива от този тип. Той съдържа всички условни формати за един диапазон и може да съдържа само три условия за формат.

FormatConditions.Add / Modify / Delete се използва във VBA за добавяне / модифициране / изтриване на FormatCondition обекти към колекцията. Всеки формат е представен от обект FormatCondition. FormatConditions е свойство на обекта Range и добавете следните параметри със синтаксис по-долу:

FormatConditions.Add (Type, Operator, Formula1, Formula2)

Синтаксисът за добавяне на формула има следните аргументи:

  • Тип: задължително, представлява дали условният формат се основава на стойност, налична в клетката или израз.
  • Оператор: По избор, представлява операторът, който ще се използва със стойност, когато „Тип“ се основава на стойността на клетката.
  • Формула1: По избор, представлява стойността или израза, свързани с условния формат.
  • Формула2: По избор, представлява стойността или израза, свързани с втората част на условния формат, когато параметърът: „Оператор“ е или „xlBetween“, или „xlNotBetween“.

FormatConditions.Modify също има същия синтаксис като FormatConditions.Add.

Следва списъкът на някои стойности / изброяване, които могат да бъдат взети от някои параметри на „Добавяне“ / „Промяна“:

Примери за условно форматиране на VBA

По-долу са примерите за условно форматиране в Excel VBA.

Пример # 1

Да кажем, че имаме файл в Excel, съдържащ име и марки на някои ученици, и искаме да определим / подчертаем маркировките като получер и син цвят, което е по-голямо от 80 и като получер и червен цвят, което е по-малко 50. Нека видим данните, съдържащи се във файла:

Използваме FormatConditions.Добавете функцията по-долу, за да постигнете това:

  • Отидете на Developer -> Visual Basic Editor:
  • Щракнете с десния бутон върху името на работната книга в прозореца „Project-VBAProject“ -> „Вмъкване“ -> „Модул“.
  • Сега напишете кода / процедурата в този модул:

Код:

Подформатиране () Край Под
  • Дефинирайте променливата rng, condition1, condition2:

Код:

Подформатиране () Dim rng As Range Dim condition1 As FormatCondition, condition2 As FormatCondition End Sub
  • Задайте / коригирайте диапазона, на който да се желае условно форматиране, като използвате функцията на VBA 'Обхват':

Код:

Подформатиране () Dim rng As Range Dim condition1 As FormatCondition, condition2 As FormatCondition Set rng = Range ("B2", "B11") End Sub
  • Изтрийте / изчистете всяко съществуващо условно форматиране (ако има такова) от диапазона, като използвате „FormatConditions.Delete“:

Код:

Подформатиране () Dim rng As Range Dim condition1 As FormatCondition, condition2 As FormatCondition Set rng = Range ("B2", "B11") rng.FormatConditions.Delete End Sub
  • Сега дефинирайте и задайте критериите за всеки условен формат, като използвате „FormatConditions.Add“:

Код:

Подформатиране () Dim rng As Range Dim condition1 As FormatCondition, condition2 As FormatCondition Set rng = Range ("B2", "B11") rng.FormatConditions.Delete Set condition1 = rng.FormatConditions.Add (xlCellValue, xlGreater, "= 80 ") Задайте условие2 = rng.FormatConditions.Add (xlCellValue, xlLess," = 50 ") End Sub
  • Определете и задайте формата, който да се прилага за всяко условие

Копирайте и поставете този код във вашия модул за клас VBA.

Код:

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

Сега, когато стартираме този код с помощта на клавиша F5 или ръчно, виждаме, че маркировките, които са по-малки от 50, се маркират в получер и червен цвят, докато тези, които са по-големи от 80, се маркират в получер и син цвят, както следва:

Забележка: Някои от свойствата за появата на форматирани клетки, които могат да се използват с FormatCondition, са:

Пример # 2

Да кажем, че в горния пример имаме и друга колона, в която се посочва, че студентът е „Топър“, ако той / тя набере повече от 80 марки, иначе Pass / Fail е написан срещу тях. Сега искаме да подчертаем стойностите, посочени като „Топър“, като получер и син. Нека видим данните, съдържащи се във файла:

В този случай кодът / процедурата ще работи както следва:

Код:

Подтекст Форматиране () Край Под

Определете и задайте формата, който да се прилага за всяко условие

Код:

Sub TextFormatting () с диапазон ("c2: c11"). FormatConditions.Add (xlTextString, TextOperator: = xlContains, String: = "topper") With .Font .Bold = True .Color = vbBlue End With End With End Sub

В горния код можем да видим, че искаме да тестваме дали диапазонът: 'C2: C11' съдържа низа: 'Topper', така че параметърът: 'Onamestor' на 'Format.Add' отвежда изброяването: 'Xcontains' до тествайте това условие във фиксирания диапазон (т.е. C2: C11) и след това направете необходимото условно форматиране (промени в шрифта) за този диапазон.

Сега, когато стартираме този код ръчно или чрез натискане на клавиша F5, виждаме, че стойностите на клетките с „Topper“ се маркират в синьо и получер:

Забележка: И така, видяхме в горните два примера как работи методът „Добавяне“ в случай на критерии за стойност на клетка (числови или текстов низ).

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.
  • За да приложим повече от три условни формата към диапазон, използвайки метода „Добавяне“, можем да използваме „Ако“ или „изберете случай“.
  • Ако методът „Добавяне“ има параметъра „Тип“ като: „xlExpression“, тогава параметърът „Оператор“ се игнорира.
  • Параметрите: „Формула1“ и „Формула2“ в метода „Добавяне“ могат да бъдат препратка към клетка, постоянна стойност, стойност на низа или дори формула.
  • Параметърът: „Формула2“ се използва само когато параметърът: „Оператор“ е или „xlBetween“, или „xlNotBetween“, иначе той се игнорира.
  • За да премахнем цялото условно форматиране от всеки работен лист, можем да използваме метода „Изтриване“, както следва:
Cells.FormatConditions.Delete

Интересни статии...