Условное форматирование макросов 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 –
Выделение ячеек на основе оператора сравнения и значения (максимум 3 условия)
Использовать FormatConditions.Добавить FormatConditions — это свойство объекта Range. Add имеет следующие параметры. Коллекция FormatConditions может содержать только 3 FormatConditions.
Параметр Type имеет следующие параметры. Для этого типа условия используйте xlCellValue.
Описание
Состояние выше среднего
Пробелы
Цветовая шкала
Условие ошибки
Условие отсутствия пробелов
Условие отсутствия ошибок
Текстовая строка
Период времени
10 главных ценностей
Уникальные значения
Параметр Operator имеет следующие параметры: xlBetween,xlEqual, xlGreater, xlGreaterEqual, xlLess, xlLessEqual, xlNotBetween или xlNotEqual. Используйте этот параметр в сочетании с параметрами Formula 1 и Formula2.
Приведенный выше код применяет условное форматирование, показанное ниже.
Выделение ячеек на основе оператора сравнения и значения (без ограничений на правила)
Чтобы применить более 3 условных форматов к диапазону, вам потребуется использовать IF или Select Case — см. код ниже.
Приведенный выше код относится к условному форматированию данных, показанных ниже.
ОБУЧЕНИЕ НА МЕСТЕ ИЛИ ОНЛАЙН
Честер был отличным наставником, понимающим наши потребности и дающим время понять и усвоить. Отличный юмор, который всегда облегчает тренировочный день. Ожидайте следующего сеанса в ближайшее время.
Подробный, информативный и веселый, Честер сделал урок приятным и увлекательным - я многому научился за время, которое мы провели вместе
Мы пригласили Честера на несколько внутренних тренингов, в том числе по основам, среднему и продвинутому уровням Excel. Он был блестящим с каждым, очень инклюзивным и терпеливым со всеми членами команды. Полностью рекомендую.
Отличное онлайн-обучение промежуточного уровня в Excel. Инструменты и упражнения, разработанные и используемые Честером, превосходны. Его стиль преподавания очень хорошо реагировал на мои потребности (применения, которые я хотел, мой стиль обучения). Я настоятельно рекомендую его - независимо от вашего уровня знаний в Excel и к какому уровню вы стремитесь!
Онлайн-курс Excel Intermediate, который я посетил, был очень качественным. Он был представлен на правильном уровне с возможностью проработать примеры в рамках курса. Учебный материал и последующая поддержка были превосходны. Я очень рекомендую.
Отличное обучение с Честером, который хорошо осведомлен и является отличным учителем. Он не торопится, чтобы убедиться, что вы поняли, прежде чем двигаться дальше, и мне посчастливилось получить специальное обучение, соответствующее моим потребностям.Большое спасибо
В прошлом году у меня был курс Excel среднего/специального уровня с Честером, и он мне так понравился, что я так высоко оценил этот курс, что впоследствии организовал для своих коллег курсы Excel для начинающих, основы, промежуточный курс и курсы Microsoft PP/word. Даже в виртуальной среде Честер четок в своих выступлениях и указаниях, исключительно хорошо осведомлен и легко адаптируется к постоянно меняющимся спискам участников или возможностям и требованиям внутри команды. Будет здорово применить наши новые навыки с пользой. Мой совет людям, будет делать заметки! Спасибо Честер! Кэтрин П.
Сотрудник недавно прошел однодневное онлайн-обучение с Честером. Ему это очень понравилось, и он получил большой опыт обучения. Полностью рекомендую Blue Pecan Computer Training и обязательно буду использовать их снова для будущих тренировок.
< /p>
Честер Тагвелл
Владелец и преподаватель курса
Blue Pecan Computer Training Limited
Зарегистрированный офис: Piper House 4 Dukes Court, Bognor Rd, Chichester, West Sussex. PO19 8FX
Условное форматирование Excel позволяет вам определять правила, определяющие форматирование ячеек.
Например, вы можете создать правило, которое выделяет ячейки, соответствующие определенным критериям. Примеры включают:
- Числа, попадающие в определенный диапазон (например, меньше 0).
- Первые 10 элементов в списке.
- Создание «тепловой карты».
- Правила, основанные на формулах, практически для любого условного форматирования.
В Excel условное форматирование можно найти на ленте в разделе Главная > Стили (ALT > H > L).
Чтобы создать собственное правило, нажмите «Новое правило», и появится новое окно:
Условное форматирование в VBA
Все эти функции условного форматирования доступны с помощью VBA.
Обратите внимание, что при настройке условного форматирования из кода VBA новые параметры появятся в окне условного форматирования внешнего интерфейса Excel и будут видны пользователю. Пользователь сможет редактировать или удалять их, если вы не заблокировали рабочий лист.
Правила условного форматирования также сохраняются при сохранении листа
Правила условного форматирования применяются конкретно к конкретному рабочему листу и к определенному диапазону ячеек. Если они нужны где-то еще в рабочей книге, их нужно настроить и на этом рабочем листе.
Практическое использование условного форматирования в VBA
У вас может быть большой объем необработанных данных, импортированных на ваш лист из файла CSV (значения, разделенные запятыми), или из таблицы базы данных, или из запроса. Это может происходить на панели инструментов или в отчете с изменением значений, импортированных из одного периода в другой.
Если число изменяется и находится за пределами допустимого диапазона, вы можете выделить это, например. цвет фона ячейки красным, и вы можете сделать это, настроив условное форматирование. Таким образом, пользователь будет мгновенно привлечен к этому номеру, а затем сможет выяснить, почему это происходит.
Вы можете использовать VBA для включения или отключения условного форматирования. Вы можете использовать VBA, чтобы очистить правила для диапазона ячеек или снова включить их. Может возникнуть ситуация, когда для необычного номера есть вполне веская причина, но когда пользователь представляет панель мониторинга или отчитывается перед вышестоящим уровнем управления, он хочет иметь возможность убрать «тревожные звоночки».
Кроме того, в необработанных импортированных данных вы можете выделить, где числа смехотворно велики, а где смехотворно малы. Диапазон импортируемых данных обычно имеет разный размер для каждого периода, поэтому вы можете использовать VBA для оценки размера нового диапазона данных и вставки условного форматирования только для этого диапазона.
Вы также можете столкнуться с ситуацией, когда есть отсортированный список имен с числовыми значениями для каждого из них, например. зарплата работника, экзаменационные оценки. При условном форматировании вы можете использовать градуированные цвета для перехода от самого высокого к самому низкому, что выглядит очень эффектно для презентаций.
Однако список имен не всегда будет статичным по размеру, и вы можете использовать код VBA для обновления шкалы градуированных цветов в соответствии с изменениями размера диапазона.
Простой пример создания условного формата в диапазоне
В этом примере задается условное форматирование для диапазона ячеек (A1:A10) на листе. Если число находится в диапазоне от 100 до 150, цвет фона ячейки будет красным, в противном случае он не будет иметь никакого цвета.
В Excel мы все использовали условное форматирование для выделения повторяющихся значений. В основном условное форматирование используется для получения повторяющихся значений. Мы можем выделять повторяющиеся значения разными способами. Мы можем выделить повторяющиеся значения, указать диапазон значений, а также определить правило для завершения критериев форматирования. Ниже приведены функции переменных, доступные в условном форматировании.
Оценка, Hadoop, Excel, мобильные приложения, веб-разработка и многое другое.
Но что, если мы сможем автоматизировать этот процесс выделения повторяющихся или любых других значений в соответствии с нашими требованиями. Критерии, которые мы можем определить с помощью условного форматирования в Excel, также можно выполнить в VBA. Для применения условного форматирования мы можем выбрать любую ячейку, диапазон, доступный на листе Excel. Условное форматирование работает только тогда, когда заданные критерии соответствуют требованию. В противном случае он не покажет никакого изменения цвета. С помощью условного форматирования в VBA мы можем изменить цвет любой ячейки или содержимого ячейки, удалить цвет ячейки или также удалить цвет. Помимо изменения цвета ячейки, мы можем изменить содержимое ячейки на жирный текст или курсив. После этого мы также можем отменить все изменения.
Как использовать условное форматирование в Excel VBA?
Ниже приведены различные примеры использования функции условного форматирования в Excel с использованием кода VBA.
Вы можете скачать этот шаблон Excel с условным форматированием VBA здесь — Шаблон Excel с условным форматированием VBA
У нас есть данные о некоторых числах и тексте, как показано ниже в столбцах A и B. Теперь мы уже классифицировали цвет, который нам нужно присвоить числу и тексту, который находится в ячейке D2. Мы определили желтый цвет для числа 1 и алфавита A и зеленый цвет для числа 2 и алфавита B.
Хотя условное форматирование VBA может быть реализовано в модуле, но написание кода для условного форматирования на листе заставит код работать только на этом листе. Для этого вместо перехода к опции «Модуль» щелкните вкладку «Вставка», чтобы вставить модуль.
Шаг 1. Теперь в первом раскрывающемся списке выберите «Рабочий лист», который по умолчанию будет общим, а в раскрывающемся списке выбора будет автоматически выбран параметр SelectionChange, как показано ниже.
Обучающий пакет 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. Вы также можете ознакомиться с другими нашими рекомендуемыми статьями –
Читайте также: