Vba excel условное форматирование
Мы можем применить условное форматирование Применить условное форматирование Условное форматирование — это метод в Excel, который позволяет нам форматировать ячейки на листе на основе определенных условий. Его можно найти в разделе стилей на вкладке «Главная». читать далее в ячейку или диапазон ячеек в Excel. Условный формат — это формат, который применяется только к ячейкам, которые соответствуют определенным критериям, например значениям выше определенного значения, положительным или отрицательным значениям или значениям с определенной формулой и т. Д. Это условное форматирование также может быть выполнено в программировании Excel VBA с использованием ‘Коллекция условий форматирования‘в макросе / процедуре.
Условие формата используется для представления условного формата, который может быть установлен путем вызова метода, возвращающего переменную этого типа. Он содержит все условные форматы для одного диапазона и может содержать только три условия форматирования.
FormatConditions.Add / Изменить / Удалить используется в VBA для добавления / изменения / удаления объектов FormatCondition в коллекцию. Каждый формат представлен объектом FormatCondition. Формат Условия является свойством объекта Range, а Добавить следующие параметры с синтаксисом ниже:
Синтаксис формулы Добавить имеет следующие аргументы:
- Тип: Обязательный, указывает, основан ли условный формат на значении, представленном в ячейке или выражении.
- Оператор: Необязательно, представляет оператор, который будет использоваться со значением, когда «Тип» основан на значении ячейки.
- Формула 1: Необязательно, представляет значение или выражение, связанное с условным форматом.
- Формула 2: Необязательно, представляет значение или выражение, связанное со второй частью условного формата, когда параметр: «Оператор» имеет значение «xlBetween» или «xlNotBetween».
FormatConditions.Modify также имеет тот же синтаксис, что и FormatConditions.Add.
Ниже приведен список некоторых значений / перечисления, которые могут использоваться некоторыми параметрами «Добавить» / «Изменить»:
Примеры условного форматирования VBA
Ниже приведены примеры условного форматирования в Excel VBA.
Вы можете скачать этот шаблон условного форматирования VBA здесь — Шаблон условного форматирования VBA
Допустим, у нас есть файл Excel, содержащий имена и оценки некоторых учащихся, и мы хотим определить / выделить оценки жирным и синим цветом, который больше 80, и жирным и красным цветом, который меньше 50. Посмотрим данные, содержащиеся в файле:
Мы используем FormatConditions. Добавьте функцию, как показано ниже, для этого:
Код:
Код:
Код:
Код:
Код:
Скопируйте и вставьте этот код в свой Модуль класса VBA Модуль класса VBA Пользователи могут создавать свои собственные объекты VBA в модулях классов VBA. Объекты, созданные в этом модуле, можно использовать в любом проекте VBA. читать далее .
Код:
Теперь, когда мы запускаем этот код с помощью клавиши F5 или вручную, мы видим, что метки, которые меньше 50, выделяются жирным и красным, а те, которые больше 80, выделяются жирным и синим цветом следующим образом:
Примечание: Некоторые из свойств внешнего вида отформатированных ячеек, которые можно использовать с FormatCondition:
Скажем, в приведенном выше примере у нас есть еще один столбец, в котором указано, что ученик является «Лучшим», если он / она набирает более 80 баллов, иначе «Сдал / Не прошел» написано против него. Теперь мы хотим выделить значения, указанные как «Топпер» жирным и синим шрифтом. Посмотрим данные, содержащиеся в файле:
В этом случае код / процедура будут работать следующим образом:
Код:
Определите и установите формат, который будет применяться для каждого условия
Код:
В приведенном выше коде мы видим, что мы хотим проверить, содержит ли диапазон: «C2: C11» строку: «Topper», поэтому параметр: «Onamestor» из «Format.Add» принимает перечисление: «Xcontains» для проверьте это условие в фиксированном диапазоне (например, C2: C11), а затем выполните необходимое условное форматирование (изменение шрифта) в этом диапазоне.
Теперь, когда мы запускаем этот код вручную или нажимая клавишу F5, мы видим, что значения ячеек с ‘Topper’ выделяются синим жирным шрифтом:
Примечание: Итак, в двух приведенных выше примерах мы видели, как метод «Добавить» работает в случае любого критерия значения ячейки (числовой или текстовой строки).
Ниже приведены некоторые другие примеры / критерии, которые можно использовать для тестирования и, таким образом, применения условного форматирования VBA к:
- Форматировать по периоду времени
- Среднее состояние
- Состояние цветовой шкалы
- IconSet условие
- Состояние панели данных
- Уникальные ценности
- Повторяющиеся значения
- Топ10 ценностей
- Процентильное состояние
- Состояние заготовок и т. Д.
При разных условиях тестирования разные значения / перечисления берутся параметрами «Добавить».
выражение Переменная, представляюная объект FormatConditions .
Параметры
Имя | Обязательный или необязательный | Тип данных | Описание |
---|---|---|---|
Тип | Обязательный | XlFormatConditionType | Указывает, основан ли условный формат на значении ячейки или выражении. |
Operator | Необязательный | Variant | Оператор условного формата. Может быть одним из следующих констант XlFormatConditionOperator : xlBetween, xlEqual, xlGreaterEqual, xlGreaterEqual, xlLess, xlLessEqual, xlNotBetween или xlNotEqual. Если Type — xlExpression, аргумент Оператора игнорируется. |
Formula1 | Необязательный | Variant | Значение или выражение, связанное с условным форматом. Может быть постоянным значением, значением строки, ссылкой ячейки или формулой. |
Formula2 | Необязательный | Variant | Значение или выражение, связанное со второй частью условного формата, когда Оператор xlBetween или xlNotBetween (в противном случае этот аргумент игнорируется). Может быть постоянным значением, значением строки, ссылкой ячейки или формулой. |
Возвращаемое значение
Объект FormatCondition , который представляет новый условный формат.
Комментарии
Чтобы изменить существующий условный формат, используйте метод Delete для удаления существующего формата перед добавлением нового.
Пример
В этом примере к ячейкам E1:E10 добавляется условный формат.
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Excel Conditional Formatting allows you to define rules which determine cell formatting.
For example, you can create a rule that highlights cells that meet certain criteria. Examples include:
- Numbers that fall within a certain range (ex. Less than 0).
- The top 10 items in a list.
- Creating a “heat map”.
- “Formula-based” rules for virtually any conditional formatting.
In Excel, Conditional Formatting can be found in the Ribbon under Home > Styles (ALT > H > L).
To create your own rule, click on ‘New Rule’ and a new window will appear:
Conditional Formatting in VBA
All of these Conditional Formatting features can be accessed using VBA.
Note that when you set up conditional formatting from within VBA code, your new parameters will appear in the Excel front-end conditional formatting window and will be visible to the user. The user will be able to edit or delete these unless you have locked the worksheet.
The conditional formatting rules are also saved when the worksheet is saved
Conditional formatting rules apply specifically to a particular worksheet and to a particular range of cells. If they are needed elsewhere in the workbook, then they must be set up on that worksheet as well.
Practical Uses of Conditional Formatting in VBA
You may have a large chunk of raw data imported into your worksheet from a CSV (comma-separated values) file, or from a database table or query. This may flow through into a dashboard or report, with changing numbers imported from one period to another.
Where a number changes and is outside an acceptable range, you may want to highlight this e.g. background color of the cell in red, and you can do this setting up conditional formatting. In this way, the user will be instantly drawn to this number, and can then investigate why this is happening.
You can use VBA to turn the conditional formatting on or off. You can use VBA to clear the rules on a range of cells, or turn them back on again. There may be a situation where there is a perfectly good reason for an unusual number, but when the user presents the dashboard or report to a higher level of management, they want to be able to remove the ‘alarm bells’.
Also, on the raw imported data, you may want to highlight where numbers are ridiculously large or ridiculously small. The imported data range is usually a different size for each period, so you can use VBA to evaluate the size of the new range of data and insert conditional formatting only for that range.
You may also have a situation where there is a sorted list of names with numeric values against each one e.g. employee salary, exam marks. With conditional formatting, you can use graduated colors to go from highest to lowest, which looks very impressive for presentation purposes.
However, the list of names will not always be static in size, and you can use VBA code to refresh the scale of graduated colors according to changes in the size of the range.
A Simple Example of Creating a Conditional Format on a Range
This example sets up conditional formatting for a range of cells (A1:A10) on a worksheet. If the number in the range is between 100 and 150 then the cell background color will be red, otherwise it will have no color.
Если вы используете в своих таблицах Excel подсветку ячеек или целых строк правилами условного форматирования, то вы, скорее всего, уже сталкивались с этой проблемой. Если ещё нет, то она поджидает вас в самом ближайшем будущем - гарантирую.
Чтобы проще было понять, в чём, собственно, дело - давайте рассмотрим простой пример. Предположим, что мы работаем вот с такой несложной таблицей, где фиксируются продажи:
Для наглядности к таблице добавлены три правила условного форматирования:
Первое правило делает синие гистограммы на столбце с суммами сделок. Создается через Главная - Условное форматирование - Гистограммы (Home - Conditional formatting - Data bars) .
Второе - подсвечивает желтым ячейки с именами менеджеров, которые не выполнили план, т.е. сумма их сделки меньше, чем зелёная ячейка H2.
Третье - делает нижнюю границу всей строки красной, если день меняется на следующий, т.е. дата в текущей строке не равна дате в следующей.
Второе и третье правила создаются через Главная - Условное форматирование - Создать правило - Использовать формулу для определения форматируемых ячеек (Home - Conditional formatting - Create rule - Use formula to determine which cells to format) с вводом соответствующей формулы (2) и настройкой формата ячеек (3):
Пока что, надеюсь, всё просто и понятно. Таблица хранит данные, а условное форматирование наглядно подсвечивает негодяев-менеджеров, разделяет даты и визуализирует стоимость.
Путь к катастрофе
Предположим, что в процессе работы с таблицей нам потребовалось удалить любую строку из середины таблицы - ну, скажем, 10-ю. После выполнения безобидного удаления получим следующую картину:
Теперь представим, что Кирилл Краснов повторил свою сделку в Тольятти с магазином "Лента" (строка 25) и вам нужно внести эти данные в таблицу.
Как вы поступите?
Скорее всего, как любой нормальный человек, вы скопируете 25-ю строчку и вставите её в конец таблицы, верно?
Ага, и получите в наследство вот такой бардак в правилах условного форматирования:
Excel зачем-то продублировал те же правила для добавленной строки вместо того, чтобы просто растянуть диапазон в поле Применяется к (Applied to) .
Ну, и на десерт давайте попробуем ещё что-нибудь безобидное - например, вставить пустую строку в середину таблицы, между 4 и 5-й строчками:
В списке правил условного форматирования это приведёт к появлению еще одного дубликата и раздроблению диапазона уже существующего 5-го правила на кучу фрагментов:
Продолжать можно долго, но, думаю, вы уже уловили идею или вспомнили, как сталкивались с этой бедой ранее (эта проблема существует в Excel ещё с 2007 года). Выполнение совершенно безобидных и естественных операций с таблицей (вставка и удаление строк, копирование, вырезание и перенос) приводят к:
Поработав пару часов с таблицей, можно закончить в ситуации, когда в из двух-трех исходных правил подсветки у вас получаются десятки и даже сотни их клонов с раздробленными диапазонами.
На англоязычных Excel-форумах в интернете такую картину называют иногда "адом" или "кошмаром условного форматирования" ("Conditional Formatting Nightmare" или "Conditional Formatting Hell").
Причем весь этот быстро разрастающийся бардак очень скоро начнёт нещадно тормозить. Условное форматирование, само по себе, весьма ресурсоёмкая штука, т.к. Excel пересчитывает правила УФ гораздо чаще, чем те же формулы. А когда этих правил несколько десятков, то даже самый мощный ПК начнёт "тупить".
Ну, и вишенкой на торте будет невозможность изменить размеры окна Диспетчера правил условного форматирования, чтобы увидеть весь этот хаос (в приведенных выше скриншотах я это сделал в графическом редакторе). Вам придется долго и мучительно прокручивать весь список в маленьком окошке полосой прокрутки.
Способ 1. Вручную
Несмотря на кажущуюся запущенность, лечится весь этот адок достаточно легко. Идея в том, что правила УФ "ломаются", обычно, для строк ниже первой. Первая же строка, в большинстве случаев, остается в порядке. Поэтому, чтобы всё починить, нам нужно просто очистить все правила в таблице и заново распространить их с первой строки на все остальные.
Для этого делаем следующее:
- Выделяем в нашей таблице все строки кроме первой.
- Удаляем все правила условного форматирования с выделенных ячеек через Главная - Условное форматирование - Удалить правила - Удалить правила из выделенных ячеек (Home - Conditional formatting - Clear rules - Clear rules from selected cells) .
- Выделяем первую строку, жмём кнопку-кисточку Формат по образцу на Главной (Home - Format Painter) и выделяем все остальные строки, копируя на них формат с первой.
Способ 2. Макросом
Если есть ощущение, что подобную процедуру вам придётся проделывать ещё не раз, то имеет смысл автоматизировать весь процесс с помощью макроса. Для этого:
- Жмём сочетание клавиш Alt + F11 или на вкладке Разработчик кнопку Visual Basic (Developer - Visual Basic) .
- В открывшемся окне редактора макросов добавляем в нашу книгу новый модуль через меню Insert - Module.
- Вставляем в созданный пустой модуль наш макрос:
Теперь можно будет просто выделить все строки в таблице (кроме шапки) и запустить макрос через Разрабочик - Макросы (Developer - Macros) или сочетанием клавиш Alt + F8 .
И всё будет хорошо :)
И не забудьте сохранить файл в формате с поддержкой макросов (xlsm).
Если нужно применять этот макрос в других файлах, то имеет смысл поместить его в Личную Книгу Макросов (Personal Macro Workbook).
Немного улучшенная версия этого макроса уже встроена в последнюю версию моей надстройки PLEX ;)
Если вы используете в своих таблицах Excel подсветку ячеек или целых строк правилами условного форматирования, то вы, скорее всего, уже сталкивались с этой проблемой. Если ещё нет, то она поджидает вас в самом ближайшем будущем - гарантирую.
Чтобы проще было понять, в чём, собственно, дело - давайте рассмотрим простой пример. Предположим, что мы работаем вот с такой несложной таблицей, где фиксируются продажи:
Для наглядности к таблице добавлены три правила условного форматирования:
Первое правило делает синие гистограммы на столбце с суммами сделок. Создается через Главная - Условное форматирование - Гистограммы (Home - Conditional formatting - Data bars) .
Второе - подсвечивает желтым ячейки с именами менеджеров, которые не выполнили план, т.е. сумма их сделки меньше, чем зелёная ячейка H2.
Третье - делает нижнюю границу всей строки красной, если день меняется на следующий, т.е. дата в текущей строке не равна дате в следующей.
Второе и третье правила создаются через Главная - Условное форматирование - Создать правило - Использовать формулу для определения форматируемых ячеек (Home - Conditional formatting - Create rule - Use formula to determine which cells to format) с вводом соответствующей формулы (2) и настройкой формата ячеек (3):
Пока что, надеюсь, всё просто и понятно. Таблица хранит данные, а условное форматирование наглядно подсвечивает негодяев-менеджеров, разделяет даты и визуализирует стоимость.
Путь к катастрофе
Предположим, что в процессе работы с таблицей нам потребовалось удалить любую строку из середины таблицы - ну, скажем, 10-ю. После выполнения безобидного удаления получим следующую картину:
Теперь представим, что Кирилл Краснов повторил свою сделку в Тольятти с магазином "Лента" (строка 25) и вам нужно внести эти данные в таблицу.
Как вы поступите?
Скорее всего, как любой нормальный человек, вы скопируете 25-ю строчку и вставите её в конец таблицы, верно?
Ага, и получите в наследство вот такой бардак в правилах условного форматирования:
Excel зачем-то продублировал те же правила для добавленной строки вместо того, чтобы просто растянуть диапазон в поле Применяется к (Applied to) .
Ну, и на десерт давайте попробуем ещё что-нибудь безобидное - например, вставить пустую строку в середину таблицы, между 4 и 5-й строчками:
В списке правил условного форматирования это приведёт к появлению еще одного дубликата и раздроблению диапазона уже существующего 5-го правила на кучу фрагментов:
Продолжать можно долго, но, думаю, вы уже уловили идею или вспомнили, как сталкивались с этой бедой ранее (эта проблема существует в Excel ещё с 2007 года). Выполнение совершенно безобидных и естественных операций с таблицей (вставка и удаление строк, копирование, вырезание и перенос) приводят к:
Поработав пару часов с таблицей, можно закончить в ситуации, когда в из двух-трех исходных правил подсветки у вас получаются десятки и даже сотни их клонов с раздробленными диапазонами.
На англоязычных Excel-форумах в интернете такую картину называют иногда "адом" или "кошмаром условного форматирования" ("Conditional Formatting Nightmare" или "Conditional Formatting Hell").
Причем весь этот быстро разрастающийся бардак очень скоро начнёт нещадно тормозить. Условное форматирование, само по себе, весьма ресурсоёмкая штука, т.к. Excel пересчитывает правила УФ гораздо чаще, чем те же формулы. А когда этих правил несколько десятков, то даже самый мощный ПК начнёт "тупить".
Ну, и вишенкой на торте будет невозможность изменить размеры окна Диспетчера правил условного форматирования, чтобы увидеть весь этот хаос (в приведенных выше скриншотах я это сделал в графическом редакторе). Вам придется долго и мучительно прокручивать весь список в маленьком окошке полосой прокрутки.
Способ 1. Вручную
Несмотря на кажущуюся запущенность, лечится весь этот адок достаточно легко. Идея в том, что правила УФ "ломаются", обычно, для строк ниже первой. Первая же строка, в большинстве случаев, остается в порядке. Поэтому, чтобы всё починить, нам нужно просто очистить все правила в таблице и заново распространить их с первой строки на все остальные.
Для этого делаем следующее:
- Выделяем в нашей таблице все строки кроме первой.
- Удаляем все правила условного форматирования с выделенных ячеек через Главная - Условное форматирование - Удалить правила - Удалить правила из выделенных ячеек (Home - Conditional formatting - Clear rules - Clear rules from selected cells) .
- Выделяем первую строку, жмём кнопку-кисточку Формат по образцу на Главной (Home - Format Painter) и выделяем все остальные строки, копируя на них формат с первой.
Способ 2. Макросом
Если есть ощущение, что подобную процедуру вам придётся проделывать ещё не раз, то имеет смысл автоматизировать весь процесс с помощью макроса. Для этого:
- Жмём сочетание клавиш Alt + F11 или на вкладке Разработчик кнопку Visual Basic (Developer - Visual Basic) .
- В открывшемся окне редактора макросов добавляем в нашу книгу новый модуль через меню Insert - Module.
- Вставляем в созданный пустой модуль наш макрос:
Теперь можно будет просто выделить все строки в таблице (кроме шапки) и запустить макрос через Разрабочик - Макросы (Developer - Macros) или сочетанием клавиш Alt + F8 .
И всё будет хорошо :)
И не забудьте сохранить файл в формате с поддержкой макросов (xlsm).
Если нужно применять этот макрос в других файлах, то имеет смысл поместить его в Личную Книгу Макросов (Personal Macro Workbook).
Немного улучшенная версия этого макроса уже встроена в последнюю версию моей надстройки PLEX ;)
Читайте также: