Vba excel условное форматирование

Обновлено: 04.07.2024

Библиотека JavaScript Excel предоставляет API для применения условного форматирования к диапазонам данных в книгах. Эта функция упрощает визуальный анализ больших наборов данных. Форматирование также динамически обновляется с учетом изменений в диапазоне.

В этой статье рассматривается условное форматирование в контексте надстроек JavaScript для Excel. В указанных ниже статьях представлены подробные сведения о всех возможностях условного форматирования в Excel.

Программное управление условным форматированием

Свойство Range.conditionalFormats — это коллекция объектов ConditionalFormat, применяемых к диапазону. Объект ConditionalFormat содержит несколько свойств, определяющих применяемый формат на основе ConditionalFormatType.

  • cellValue
  • colorScale
  • custom
  • dataBar
  • iconSet
  • preset
  • textComparison
  • topBottom

У каждого из этих свойств форматирования есть соответствующий вариант *OrNullObject . Дополнительные статьи об этом шаблоне в разделе * Методы OrNullObject.

Для объекта ConditionalFormat можно установить только один тип формата. Это определено свойством type , которое является значением перечисления объекта ConditionalFormatType. Параметр type устанавливается при добавлении условного форматирования к диапазону.

Создание правил условного форматирования

Условное форматирование добавляется к диапазону с помощью conditionalFormats.add . После добавления можно задать свойства, относящиеся к условному форматированию. В примерах ниже показано создание различных типов форматирования.

Значение ячейки

При условном форматировании значения ячейки применяется пользовательский формат на основе результатов одной или двух формул в ConditionalCellValueRule. Свойство operator является оператором ConditionalCellValueOperator, который определяет, как итоговое выражение связано с форматированием.

В приведенном ниже примере показано применение красного шрифта ко всем значениям диапазона, которые меньше нуля.

Диапазон с отрицательными числами красного цвета.

Цветовая шкала

При условном форматировании с использованием цветовой шкалы применяется цветовой градиент в диапазоне данных. Свойство criteria в ColorScaleConditionalFormat определяет три точки ConditionalColorScaleCriterion: minimum , maximum и (при желании) midpoint . У каждой точки условия есть три свойства:

  • color — HTML-код цвета для конечной точки.
  • formula — число или формула, представляющая значение конечной точки. Оно будет равным null , если type имеет значение lowestValue или highestValue .
  • type — способ оценки формулы. highestValue и lowestValue относятся к значениям в форматируемом диапазоне.

В приведенном ниже примере показан диапазон, окрашенный с переходом от синего к желтому и красному цвету. Обратите внимание, что minimum и maximum являются минимальным и максимальным значением соответственно, и для них используются формулы null . Для значения midpoint используется тип percentage с формулой "=50" , чтобы самая желтая ячейка соответствовала среднему значению.

Диапазон с низким значением синего цвета, средним значением желтого цвета, высоким значением красного цвета и применением градиента для промежуточных значений.

Пользовательское

При пользовательском условном форматировании применяется пользовательский формат к ячейкам на основе формулы произвольной сложности. Объект ConditionalFormatRule позволяет определять формулу в разных нотациях:

  • formula — стандартная нотация.
  • formulaLocal - Локализовано на основе языка пользователя.
  • formulaR1C1 — нотация R1C1.

В приведенном ниже примере зеленым цветом окрашен шрифт ячеек с более высокими значениями, чем в ячейках слева.

Диапазон с числами, окрашенными в зеленый цвет, если значение в предшествующем столбце этой строки ниже.

Гистограмма

При условном форматировании с использованием гистограмм они добавляются к ячейкам. По умолчанию минимальное и максимальное значения в диапазоне создают границы и пропорциональные размеры гистограмм. Объект DataBarConditionalFormat обладает несколькими свойствами для управления внешним видом панели.

В приведенном ниже примере используется форматирование с помощью гистограмм с заполнением слева направо.

Диапазон с гистограммами позади значений в ячейках.

Набор значков

При условном форматировании с набором значков используются значки Excel для выделения ячеек. Свойство criteria — это массив объекта ConditionalIconCriterion, определяющий добавляемый символ и условия для добавления. Этот массив автоматически заполняется элементами условия со свойствами по умолчанию. Отдельные свойства не могут быть перезаписаны. Вместо этого необходимо заменить весь объект условия.

В приведенном ниже примере показано применение в диапазоне набора из трех значков с треугольниками.

Готовые условия

При условном форматировании с готовыми условиями применяется пользовательский формат к диапазону на основе выбранного стандартного правила. Эти правила определяются с помощью ConditionalFormatPresetCriterion в ConditionalPresetCriteriaRule.

В следующем примере цвет шрифта белый, где значение ячейки по крайней мере на одно стандартное отклонение выше среднего диапазона.

Диапазон с белым шрифтом в ячейках со значениями, превышающими среднее значение хотя бы на одно стандартное отклонение.

Сравнение текста

При условном форматировании со сравнением текста используется сравнение строк в качестве условия. Свойство rule является объектом ConditionalTextComparisonRule, определяющим строку для сравнения с ячейкой и оператор для указания типа сравнения.

В следующем примере форматы цвета шрифта красный, когда текст ячейки содержит "Задержки".

Диапазон с ячейками, содержащими слово Delayed красного цвета.

Верхнее или нижнее значение

При условном форматировании верхнего или нижнего значения применяется форматирование к наибольшему или наименьшему значению в диапазоне. Свойство rule , являющееся типом ConditionalTopBottomRule, указывает основание для условия (максимальное или минимальное значение), а также применение ранжированной или процентной оценки.

В приведенном ниже примере применяется зеленое выделение к ячейке с максимальным значением в диапазоне.

Диапазон с максимальным числом, выделенным зеленым цветом.

Разные форматирования и приоритет

К диапазону можно применять несколько типов условного форматирования. Если форматы содержат конфликтующие элементы, например разный цвет шрифта, только один формат применяет этот конкретный элемент. Приоритет определяется свойством ConditionalFormat.priority . Приоритет — это число (равное индексу в ConditionalFormatCollection ), которое можно установить при создании формата. Чем ниже значение priority , тем выше приоритет формата.

В приведенном ниже примере показан выбор цвета шрифта при конфликте между двумя форматами. Для отрицательных чисел применяется полужирный шрифт, но НЕ красный, так как приоритет получает формат, устанавливающий для них синий цвет шрифта.

Диапазон с использованием для низких чисел полужирного красного шрифта, а для отрицательных — синего шрифта с зеленым фоном.

Взаимоисключающие условные форматирования

Свойство stopIfTrue объекта ConditionalFormat не позволяет применять к диапазону условное форматирование с более низким приоритетом. Если при сопоставлении с диапазоном применяется условное форматирование со свойством stopIfTrue === true , последующие условные форматирования не применяются, даже если их элементы не вступают в противоречие.

В приведенном ниже примере показано добавление в диапазон двух условных форматов. Для отрицательных чисел будет использоваться синий шрифт со светло-зеленым фоном, независимо от того, выполняются ли условия другого формата.


Условное форматирование для пустых ячеек - это функция в Excel, которая используется для создания встроенного или настраиваемого форматирования. Исходя из этого, мы можем выделить дубликаты, покрасить ячейку в соответствии с различным диапазоном значений и т. Д. Также есть способ выделить пустые ячейки.

Как применить условное форматирование для пустых ячеек?

Применить условное форматирование для пустых ячеек очень просто и легко. Давайте разберемся, как подать заявку в Excel.

Вы можете скачать это условное форматирование для шаблона Excel пустых ячеек здесь - Условное форматирование для шаблона Excel пустых ячеек

Условное форматирование для пустых ячеек - пример № 1

Мы рассмотрим небольшую часть условного форматирования, которая выделяет пустые ячейки. Для этого рассмотрим чистый лист. Это лучший способ просмотра и применения условного форматирования для чистого листа или некоторых ячеек чистого листа. Если мы применим условное форматирование для заполнения чистого листа или некоторых его ячеек, мы сможем увидеть, как эта ячейка выделяется. Для этого мы выбрали небольшую часть листа, покрытую толстой рамкой, как показано ниже.


  • Мы будем применять условное форматирование только в определенном регионе. Для этого перейдите в главное меню и выберите « Условное форматирование» в разделе «Стили», как показано ниже.


  • Как только мы это сделаем, мы получим выпадающий список условного форматирования. Из этого списка выберите Новое правило .


  • Откроется окно нового правила форматирования . Там у нас есть другое правило для применения условного форматирования. Но для Пустая ячейка выберите второй вариант, который Форматировать только те ячейки, которые содержат.


  • И ниже в поле «Редактировать описание правила» мы должны определить различные критерии. Здесь, в самом первом окне раскрывающегося списка, выберите « Пробелы» в качестве значения ячейки.


  • Как только мы выберем Пробелы в качестве значения ячейки, все остальные выпадающие списки будут исключены из условия. И мы получим условия, связанные с пустыми ячейками.


  • Теперь для дальнейшего, нажмите на опцию Формат в том же окне, как показано на скриншоте ниже.


  • Опция «Формат» приведет нас к окнам настройки, где мы можем изменить текстовые шрифты в форме или определить или изменить границу или заливку. Для выделения пустых ячеек перейдите на вкладку « Заливка » и выберите любой желаемый цвет согласно требованию. Мы также можем изменить шаблон. После этого нажмите Ok, как показано ниже.


  • После нажатия Ok, мы снова вернемся к предыдущему окну, где мы получим предварительный просмотр выбранного цвета и состояния, как показано ниже.


  • Если выбранное условие соответствует и соответствует требованию, нажмите кнопку «ОК» или выберите «Вернуться», чтобы обновить условия. Здесь мы должны применять условия и правила в соответствии с нашими потребностями. Теперь нажмите на ОК .


  • После нажатия Ok, все пустые ячейки будут выделены желтым цветом, как показано ниже.


  • Теперь, чтобы проверить, правильно ли применено выбранное нами условие, перейдите к любой из этих ячеек и введите что-нибудь, чтобы увидеть, изменится ли цвет ячейки на «Нет заливки» или «Белый фон». Как мы видим, ячейки с любым значением теперь заменены на ячейку без заполнения, как показано ниже.


Условное форматирование для пустых ячеек - пример № 2

Есть еще один способ применить условное форматирование к пустым ячейкам. И этот метод довольно прост в применении. Для этого у нас есть другой набор данных, как показано ниже.


Теперь для применения условного форматирования сначала выберите данные и следуйте тому же пути, как показано в примере-1.

  • Перейдите в главное меню, в разделе «Стили» выберите « Условное форматирование» .


  • Как только мы это сделаем, мы получим раскрывающийся список всех доступных вариантов под ним. Теперь выберите Новое правило из списка.


  • Как только мы это сделаем, мы получим поле Новое правило форматирования. Там у нас есть другое правило для применения условного форматирования. Но здесь мы должны выбрать последний вариант: « Использовать формулу, чтобы определить, какие ячейки форматировать».


  • Теперь в поле Edit the Rule Description введите синтаксис функции ISBLANK и выберите полный диапазон данных, а затем нажмите на вкладку Format, как показано ниже.


  • Теперь откроется окно «Формат ячеек». Перейдите на вкладку Fill .



  • После нажатия Ok, мы снова вернемся к тому же предыдущему окну, где мы получим предварительный просмотр выбранного цвета и состояния. Теперь нажмите Ok, чтобы применить.


  • Чтобы проверить примененное условие, удалите любые данные ячейки и посмотрите результат. Для тестирования мы удалили данные некоторых ячеек, и цвет этих ячеек изменился с No Fill на Red Peach. Что показывает, что наши выбранные и применяемые условия работают должным образом.


Преимущества применения условного форматирования для пустых ячеек

  • Это очень быстро и легко наносится.
  • Мы можем выбрать любой диапазон и тип данных, чтобы выделить пустые ячейки.
  • Это очень полезно, когда мы работаем над проверкой данных. Этим мы можем выделить ячейку и атрибуты, которые оставлены пустыми.

Минусы того, как применять условное форматирование для пустых ячеек

  • Применение условного форматирования к большому набору данных, например к полному листу, может замедлить работу Excel при фильтрации.

То, что нужно запомнить

  • Выбор правильного правила и форматирования очень важен. Как только мы выберем, он всегда будет проверять и искать предварительный просмотр перед применением изменений.
  • Всегда используйте ограниченные данные, если вы имеете дело и применяете большее условное форматирование, чтобы избежать зависания Excel.

Рекомендуемые статьи

Это было руководство по условному форматированию для пустых ячеек. Здесь мы обсудим, как применить условное форматирование для пустых ячеек, а также приведем практические примеры и загружаемый шаблон Excel. Вы также можете просмотреть наши другие предлагаемые статьи -


В Excel мы все использовали условное форматирование для выделения дублирующихся значений. В основном условное форматирование используется для получения дублированных значений. Мы можем выделить дубликаты значений разными способами. Мы можем выделить дубликаты значений, диапазон конкретных значений, а также определить правило для завершения критериев форматирования. Ниже приведены функции переменных, доступные в разделе «Условное форматирование».


Но что, если мы сможем автоматизировать этот процесс выделения дубликатов или любых других значений в соответствии с нашим требованием. Критерии, которые мы можем определить с помощью условного форматирования в Excel, также могут быть выполнены в VBA. Для применения условного форматирования мы можем выбрать любую ячейку, диапазон которой доступен на листе Excel. Условное форматирование работает только тогда, когда определенные критерии соответствуют требованию. Иначе, это не покажет никакого изменения цвета. С помощью условного форматирования в VBA мы можем изменить цвет любой ячейки или содержимого ячейки, удалить цвет ячейки или удалить цвет. Помимо изменения цвета ячейки, мы можем изменить содержимое ячейки на текст, выделенный жирным шрифтом или курсивом . После этого мы можем отменить все изменения.

Как использовать условное форматирование в Excel VBA?

Ниже приведены различные примеры использования функции условного форматирования в Excel с использованием кода VBA.

Вы можете скачать этот шаблон Excel для условного форматирования VBA здесь - Шаблон Excel для условного форматирования VBA

Условное форматирование VBA - пример № 1

У нас есть данные о некоторых числах и тексте, как показано ниже в столбцах A и B. Теперь мы уже классифицировали цвет, который нам нужно присвоить числу и тексту, который находится в ячейке D2. Мы определили желтый цвет для номера 1 и алфавита A и зеленый цвет для номера 2 и алфавита B.


Хотя условное форматирование VBA может быть реализовано в модуле, но написание кода для условного форматирования на листе заставит код работать только на этом листе. Для этого вместо того, чтобы перейти к опции «Модуль», нажмите на вкладку «Вставка», чтобы вставить модуль.


Шаг 1: Теперь в первом раскрывающемся списке выберите « Рабочий лист», который по умолчанию будет общим, и в раскрывающемся списке «Выбор» он автоматически выберет параметр SelectionChange, как показано ниже.


Шаг 2: Как только мы это сделаем, он автоматически активирует частную подкатегорию, и целевая ячейка будет в диапазоне.

Код:


Шаг 3: Теперь напишите код, сначала определите переменную MyRange как Range . Или вы можете выбрать любое другое имя вместо MyRange согласно вашему выбору.

Код:


Шаг 4: Используйте Set и выберите определенный диапазон, как показано ниже.

Код:


Шаг 5: После этого выберите Рабочий лист, к которому мы хотим применить условное форматирование. Здесь наш лист Sheet1. Мы можем поставить последовательность также как 1 вместо записи Sheet1. А затем выберите диапазон тех ячеек, которые нам нужно отформатировать. Здесь наш диапазон от ячейки A1 до B8.

Код:


Шаг 6: Теперь откройте цикл For Each-Next, как показано ниже. И начнем с выбора переменной MyRange, определенной в Cell .

Код:


Шаг 7: Теперь снова откройте цикл If-Else.

Код:


Это регион, в котором мы назначаем цвета всем числам и алфавитам, имеющимся в нашем ассортименте.

Шаг 8: Напишите код, если значение ячейки равно 1, тогда цвет интерьера: выбранная ячейка диапазона от A1 до B8 будет зеленого цвета. А для зеленого у нас есть цветовой код, назначенный ему как 6.

Код:


Шаг 9: Теперь для значения ячейки номер 2. Иначе, если значение ячейки любой ячейки из выбранного диапазона равно 2, то цвет внутренней части этой ячейки будет желтым. А для желтого у нас есть код цвета, назначенный ему как 4.

Код:


Для каждого цвета у нас есть разные цветовые коды, назначенные им, которые начинаются с 1 до 56. Принимая во внимание, что числовой код 1 назначается черному цвету, а номер 56 назначается темно-серому цвету. Между прочим, у нас есть другие цвета, которые мы можем найти в Microsoft Documents.

Шаг 10: Если что-либо из перечисленного

условие - ЛОЖЬ, тогда у нас будет другое условие, если если значение ячейки равно А, то внутренний цвет ячейки будет Желтым. И для желтого снова мы назначим код как 6.

Код:


Шаг 11: Сделайте то же самое для значения ячейки B, с цветовым кодом 4, как зеленый.

Код:


Шаг 12: Если какое-либо из условий не TRUE, то для Else мы предпочтем выбрать цветовой код как None .

Код:


Шаг 13: Поскольку код большой, для компиляции каждого шага кода нажмите функциональную клавишу F8. Если ошибки не найдены, нажмите кнопку воспроизведения, чтобы запустить весь код за один раз. Мы увидим, что согласно правилу условного форматирования, определенному в коде VBA, цвет ячеек был изменен на выбранные цветовые коды, как показано ниже.


Шаг 14: Это форматирование теперь исправлено. Если мы хотим увидеть изменения в цвете, для теста давайте изменим значение любой ячейки, считая А1 с 1 на 2. Мы увидим, что цвет ячейки А1 меняется на Зеленый.


Это связано с тем, что мы объявили, что в диапазоне от A1 до B8 любая ячейка, содержащая числа 1 и 2 и алфавиты A и B, будет отформатирована в желтый и зеленый цвета, как показано в ячейках D2-E3.

Плюсы и минусы

  • Это дает мгновенный вывод, если у нас есть огромные данные. Принимая во внимание, что если мы применим то же самое из пункта меню Excel, потребуется время, чтобы очистить форматирование для большого набора данных.
  • Мы можем выполнять все типы функций, которые доступны в Excel для условного форматирования в VBA.
  • Не рекомендуется применять условное форматирование VBA для небольшого набора данных.

То, что нужно запомнить

  • Есть много других функций, кроме выделения дубликатов и одинаковых ячеек значений. Мы можем изменить формат ячейки любым способом, таким как полужирный шрифт, текст курсивом, изменение цвета шрифта, изменение цвета фона, выделение значений в некотором определенном диапазоне.
  • После применения условного форматирования мы можем изменить правило, фактически мы можем также удалить условия форматирования. Так что наши данные вернутся к норме.
  • Мы можем применить более одного условия в одном макросе.

Рекомендуемые статьи

Это руководство по условному форматированию VBA. Здесь мы обсудим, как использовать функцию условного форматирования Excel VBA вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи -

Office 365 ProPlus переименован в Майкрософт 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 запущенной версии.

Читайте также: