Условное форматирование Vba Excel
Обновлено: 05.07.2024
Мы можем применить условное форматирование Применение условного форматирования Условное форматирование — это метод в Excel, который позволяет нам форматировать ячейки на листе на основе определенных условий. Его можно найти в разделе стилей на вкладке «Главная». читать больше в ячейку или диапазон ячеек в Excel. Условный формат — это формат, который применяется только к ячейкам, которые соответствуют определенным критериям, например значениям выше определенного значения, положительным или отрицательным значениям или значениям с определенной формулой и т. д. Это условное форматирование также может быть выполнено в программе Excel VBA с использованием "Коллекция условий формата" в макросе/процедуре.
Условие формата используется для представления условного формата, который можно задать, вызвав метод, возвращающий переменную этого типа. Он содержит все условные форматы для одного диапазона и может содержать только три условия формата.
FormatConditions.Add/Modify/Delete используется в VBA для добавления/изменения/удаления объектов FormatCondition в коллекцию. Каждый формат представлен объектом FormatCondition. FormatConditions — это свойство объекта Range, и добавьте следующие параметры с синтаксисом ниже:
Синтаксис формулы Добавить имеет следующие аргументы:
- Тип: обязательный, указывает, основан ли условный формат на значении, присутствующем в ячейке, или на выражении.
- Оператор: необязательный, представляет оператор, который будет использоваться со значением, когда «Тип» основан на значении ячейки.
- Формула1: необязательно, представляет значение или выражение, связанное с условным форматом.
- Формула2: необязательно, представляет значение или выражение, связанное со второй частью условного формата, когда параметр: «Оператор» имеет значение «xlBetween» или «xlNotBetween».
FormatConditions.Modify также имеет тот же синтаксис, что и FormatConditions.Add.
Ниже приведен список некоторых значений/перечислений, которые могут приниматься некоторыми параметрами «Добавить»/«Изменить»:
Примеры условного форматирования VBA
Ниже приведены примеры условного форматирования в Excel VBA.
Допустим, у нас есть файл Excel, содержащий имена и оценки некоторых учащихся, и мы хотим определить/выделить оценки жирным и синим цветом, что больше 80, а также жирным и красным цветом, что меньше 50. Посмотрим, какие данные содержатся в файле:
Для этого мы используем функцию FormatConditions.Add, как показано ниже:
Код:
Код:
Код:
Код:
Код:
Код:
Теперь, когда мы запускаем этот код с помощью клавиши F5 или вручную, мы видим, что отметки меньше 50 выделены жирным шрифтом и красным цветом, а отметки больше 80 выделены жирным шрифтом и синим цветом следующим образом:< /p>
Допустим, в приведенном выше примере у нас есть еще один столбец, в котором также указано, что учащийся является «лучшим», если он / она набирает более 80 баллов, в противном случае против него написано «зачет/незачет». Теперь мы хотим выделить значения, указанные как «Topper», жирным и синим цветом. Давайте посмотрим данные, содержащиеся в файле:
В этом случае код/процедура будет работать следующим образом:
Код:
Определить и установить формат, который будет применяться для каждого условия
Код:
В приведенном выше коде мы видим, что мы хотим проверить, содержит ли диапазон: «C2:C11» строку: «Topper», поэтому параметр: «Onamestor» в «Format.Add» принимает перечисление: Xcontains», чтобы проверить это условие в фиксированном диапазоне (например, C2:C11), а затем выполнить требуемое условное форматирование (изменение шрифта) в этом диапазоне.
Теперь, когда мы запускаем этот код вручную или нажимая клавишу F5, мы видим, что значения ячеек с надписью "Верх" выделяются синим цветом и жирным шрифтом:
Примечание. Итак, в двух приведенных выше примерах мы видели, как метод «Добавить» работает в случае любого критерия значения ячейки (числовой или текстовой строки).
Ниже приведены некоторые другие экземпляры/критерии, которые можно использовать для тестирования и, таким образом, применения условного форматирования VBA к:
- Форматировать по периоду времени
- Среднее состояние
- Условие цветовой шкалы
- Условие IconSet
- Состояние панели данных
- Уникальные значения
- Повторяющиеся значения
- 10 главных ценностей
- Процентное условие
- Пробелы и т. д.
При различных тестируемых условиях разные значения/перечисления принимаются параметрами «Добавить».
Что следует помнить об условном форматировании VBA
- Метод «Добавить» с «FormatConditions» используется для создания нового условного формата, метод «Удалить» для удаления любого условного формата и метод «Изменить» для изменения любого существующего условного формата.
- Метод «Добавить» с «Коллекцией FormatConditions» не работает, если для одного диапазона создано более трех условных форматов.
- Чтобы применить более трех условных форматов к диапазону с помощью метода "Добавить", мы можем использовать "Если" или "выбрать регистр".
- Если метод "Добавить" имеет параметр "Тип" как "xlExpression", то параметр "Оператор" игнорируется.
- Параметры: «Формула1» и «Формула2» в методе «Добавить» могут быть ссылкой на ячейку, постоянным значением, строковым значением или даже формулой.
- Параметр: «Формула2» используется только в том случае, если параметр «Оператор» имеет значение «xlBetween» или «xlNotBetween», в противном случае он игнорируется.
- Чтобы удалить все условное форматирование с любого рабочего листа, мы можем использовать метод «Удалить» следующим образом:
Рекомендуемые статьи
Это руководство по условному форматированию VBA. Здесь мы узнаем, как применить условное форматирование к ячейке Excel, используя метод условий форматирования в VBA, а также практические примеры и загружаемый шаблон. Ниже вы можете найти несколько полезных статей по Excel VBA –
выражение Переменная, представляющая объект FormatConditions.
Параметры
| Имя | Обязательный/Необязательный | Тип данных | Описание |
|---|---|---|---|
| Тип | Обязательный | XlFormatConditionType | Указывает, основано ли условное форматирование на значении ячейки или на выражении. |
| Оператор | Необязательно | Вариант | Оператор условного форматирования. Может быть одной из следующих констант XlFormatConditionOperator: xlBetween, xlEqual, xlGreater, xlGreaterEqual, xlLess, xlLessEqual, xlNotBetween или xlNotEqual. Если Type имеет значение xlExpression, аргумент Operator игнорируется. |
| < em>Формула1 | Необязательно | Вариант | Значение или выражение, связанное с условным форматом. Может быть постоянным значением, строковым значением, ссылкой на ячейку или формулой. |
| Формула2 | Необязательно | Вариант | Значение или выражение, связанное со второй частью условного формата, когда Operator имеет значение xlBetween или xlNotBetween (в противном случае этот аргумент игнорируется). Может быть постоянным значением, строковым значением, ссылкой на ячейку или формулой. |
Возвращаемое значение
Объект FormatCondition, представляющий новый условный формат.
Примечания
Используйте метод Modify, чтобы изменить существующий условный формат, или используйте метод Delete, чтобы удалить существующий формат перед добавлением нового.
Пример
В этом примере условное форматирование добавляется в ячейки E1:E10.
Поддержка и обратная связь
Есть вопросы или отзывы об Office VBA или этой документации? См. раздел Поддержка и отзывы об Office VBA, чтобы узнать, как получить поддержку и оставить отзыв.
Office 365 профессиональный плюс переименовывается в Приложения Microsoft 365 для предприятий. Для получения дополнительной информации об этом изменении прочитайте эту запись в блоге.
Симптомы
Рассмотрите следующий сценарий.
- Вы выбираете ячейку на листе в Microsoft Excel 2000 или более поздней версии Excel.
- Вы используете Microsoft Visual Basic для приложений (VBA) для создания программы условного форматирования на основе формул.
- Вы используете относительные ссылки на ячейки в программе условного форматирования VBA.
- Вы применяете условное форматирование к ячейке, отличной от выбранной.
Когда вы применяете условное форматирование, вы замечаете, что условное форматирование задано неправильно.
Например, эта проблема возникает при использовании программы, которая включает в себя код VBA на листе Excel, похожий на следующий код:
Этот код автоматически применяет условное форматирование к ячейке B1, когда вы вводите «1» в ячейку A1. Когда вы вводите «1» в ячейку A1, вы ожидаете, что цвет ячейки B1 изменится на красный. При этом цвет клетки не меняется. Цвет ячейки B1 изменится на красный, только если вы введете «1» в ячейку B1.
Кроме того, в диалоговом окне "Условное форматирование" формула отображается как =B1=1 вместо =A1=1.
Временное решение
Чтобы обойти эту проблему, воспользуйтесь одним из следующих способов.
Способ 1. Используйте абсолютные ссылки на ячейки
Вы можете использовать абсолютные ссылки на ячейки, чтобы ссылаться на ячейку, содержащую формулу, вместо того, чтобы ссылаться на относительные ссылки на ячейки.
Например, вы можете изменить текстовую запись Formula1:="=A1=1" в коде VBA, которая описана в разделе "Проблема" как Formula1:="=$A$1=1", чтобы код использовать абсолютные ссылки на ячейки. Эта модифицированная версия кода VBA выглядит следующим образом:
Способ 2. Выберите ячейку, которую вы хотите использовать для условного форматирования, прежде чем применять формулу
Если вы хотите применить условное форматирование к ячейке, сначала выберите ячейку, которую вы хотите использовать для условного форматирования. Затем выберите ячейку, которую вы хотите использовать для формулы. Выбрав эту ячейку, измените формулу в соответствии с вашими требованиями.
Для этого выполните следующие действия в зависимости от используемой версии Excel.
В Excel мы все использовали условное форматирование для выделения повторяющихся значений. В основном условное форматирование используется для получения повторяющихся значений. Мы можем выделять повторяющиеся значения разными способами. Мы можем выделить повторяющиеся значения, указать диапазон значений, а также определить правило для выполнения критериев форматирования. Ниже приведены функции переменных, доступные в условном форматировании.
Оценка, Hadoop, Excel, мобильные приложения, веб-разработка и многое другое.
Но что, если мы сможем автоматизировать этот процесс выделения повторяющихся или любых других значений в соответствии с нашими требованиями. Критерии, которые мы можем определить с помощью условного форматирования в Excel, также можно выполнить в VBA. Для применения условного форматирования мы можем выбрать любую ячейку, диапазон, доступный на листе Excel. Условное форматирование работает только тогда, когда заданные критерии соответствуют требованиям. В противном случае он не покажет никакого изменения цвета. С помощью условного форматирования в VBA мы можем изменить цвет любой ячейки или содержимого ячейки, удалить цвет ячейки или также удалить цвет. Помимо изменения цвета ячейки, мы можем изменить содержимое ячейки на жирный текст или курсив. После этого мы также можем отменить все изменения.
Как использовать условное форматирование в Excel VBA?
Ниже приведены различные примеры использования функции условного форматирования в Excel с использованием кода VBA.
Вы можете скачать этот шаблон Excel с условным форматированием VBA здесь — Шаблон Excel с условным форматированием VBA
У нас есть данные о некоторых числах и тексте, как показано ниже в столбцах A и B. Теперь мы уже классифицировали цвет, который нам нужно присвоить числу и тексту, который находится в ячейке D2. Мы определили желтый цвет для числа 1 и алфавита A и зеленый цвет для числа 2 и алфавита B.
Хотя условное форматирование VBA может быть реализовано в модуле, но написание кода для условного форматирования на листе заставит код работать только на этом листе. Для этого вместо перехода к опции «Модуль» щелкните вкладку «Вставка», чтобы вставить модуль.
Шаг 1. Теперь в первом раскрывающемся списке выберите «Рабочий лист», который по умолчанию будет общим, а в раскрывающемся списке автоматически будет выбран вариант «Изменение выбора», как показано ниже.
Обучающий пакет MS Excel 13 онлайн-курсов | 100+ часов | Поддающиеся проверке сертификаты | Срок действия
4,5 (303 оценки)
Шаг 2. Как только мы это сделаем, будет автоматически активирована частная подкатегория, а целевая ячейка станет диапазоном.
Код:
Шаг 3. Теперь напишите код, сначала определите переменную MyRange как Range. Или вы можете выбрать любое другое имя вместо MyRange по вашему выбору.
Код:
Шаг 4. Используйте Set и выберите определенный диапазон, как показано ниже.
Код:
Шаг 5. После этого выберите рабочий лист, к которому мы хотим применить условное форматирование. Здесь наш лист Sheet1. Мы можем поставить последовательность также как 1 вместо того, чтобы писать Sheet1. А затем выделяем диапазон тех ячеек, которые нам нужно отформатировать. Здесь наш диапазон от ячейки A1 до B8.
Код:
Шаг 6. Теперь откройте цикл For Each-Next, как показано ниже. И начните это с выбора переменной MyRange, определенной ячейкой.
Код:
Шаг 7. Теперь снова откройте цикл If-Else.
Код:
Это область, в которой мы будем назначать цвета всем числам и алфавитам, доступным в нашем диапазоне.
Шаг 8: Напишите код, если значение ячейки равно 1, то внутренний цвет выбранной ячейки диапазона от A1 до B8 будет зеленым. А для зеленого у нас есть цветовой код 6.
Код:
Шаг 9. Теперь для значения ячейки номер 2. В противном случае, если значение ячейки любой ячейки из выбранного диапазона равно 2, внутренний цвет этой ячейки будет желтым. А желтому цвету присвоен код цвета 4.
Код:
Каждому цвету присвоены разные цветовые коды, начиная с 1 и заканчивая 56. В то время как числовой код 1 назначается черному цвету, а номер 56 — темно-серому цвету. Между ними у нас есть другие цветовые оттенки, которые мы можем найти в документах Microsoft.
Шаг 10. Если что-то из вышеперечисленного
условие FALSE, тогда у нас будет другое условие Else if, где, если значение ячейки равно A, тогда внутренний цвет ячейки будет желтым. И снова для желтого мы назначим код 6.
Код:
Шаг 11. Проделайте то же самое со значением ячейки B, присвоив коду 4 зеленый цвет.
Код:
Шаг 12. Если какое-либо из условий НЕ ИСТИННО, тогда для параметра "Другое" мы предпочтем выбрать цветовой код "Нет".
Код:
Шаг 13. Поскольку код большой, для компиляции каждого шага кода нажимайте функциональную клавишу F8. Если ошибок не обнаружено, нажмите кнопку воспроизведения, чтобы запустить весь код за один раз.Мы увидим, что в соответствии с правилом условного форматирования, определенным в коде VBA, цвет ячеек был изменен на выбранные цветовые коды, как показано ниже.
Шаг 14. Исправлено форматирование. Если мы хотим увидеть изменения в цвете, для теста давайте изменим значение любой ячейки A1 с 1 на 2. Мы увидим, что цвет ячейки A1 изменился на зеленый.
Это связано с тем, что мы объявили, что в диапазоне от A1 до B8 любая ячейка, содержащая числа 1 и 2 и алфавиты A и B, будет отформатирована как желтый и зеленый цвета, как показано в ячейках от D2 до E3.
За и против
- Это дает мгновенный результат, если у нас есть огромные данные. Принимая во внимание, что если мы применим то же самое из пункта меню Excel, потребуется время, чтобы очистить форматирование для большого набора данных.
- Мы также можем выполнять все типы функций, которые доступны в Excel для условного форматирования в VBA.
- Не рекомендуется применять условное форматирование VBA к небольшому набору данных.
Что нужно помнить
- Помимо выделения дубликатов и ячеек с одинаковыми значениями существует множество других функций. Мы можем изменить формат ячейки любым способом, например выделить жирный шрифт, курсив, изменить цвет шрифта, изменить цвет фона, выделить значения в определенном диапазоне.
- После применения условного форматирования мы можем изменить правило, фактически мы также можем удалить условия форматирования. Так что наши данные вернутся к норме.
- В одном макросе можно применить более одного условия.
Рекомендуемые статьи
Это руководство по условному форматированию VBA. Здесь мы обсуждаем, как использовать функцию условного форматирования Excel VBA вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете ознакомиться с другими нашими рекомендуемыми статьями –
Читайте также: