Перейти к содержимому
Наше приложение «Дешевые авиабилеты» в AppGallery >>

VBA Excel. Цвет ячейки (заливка, фон)

    Заливка ячейки цветом в VBA Excel. Фон ячейки. Свойства .Interior.Color и .Interior.ColorIndex. Цветовая модель RGB. Стандартная палитра. Очистка фона ячейки.

    Свойство .Interior.Color объекта Range

    Начиная с Excel 2007 основным способом заливки диапазона или отдельной ячейки цветом (зарисовки, добавления, изменения фона) является использование свойства .Interior.Color объекта Range путем присваивания ему значения цвета в виде десятичного числа от 0 до 16777215 (всего 16777216 цветов).

    Заливка ячейки цветом в VBA Excel

    Пример кода 1:

    Поместите пример кода в свой программный модуль и нажмите кнопку на панели инструментов «Run Sub» или на клавиатуре «F5», курсор должен быть внутри выполняемой программы. На активном листе Excel ячейки и диапазон, выбранные в коде, окрасятся в соответствующие цвета.

    Есть один интересный нюанс: если присвоить свойству .Interior.Color отрицательное значение от -16777215 до -1, то цвет будет соответствовать значению, равному сумме максимального значения палитры (16777215) и присвоенного отрицательного значения. Например, заливка всех трех ячеек после выполнения следующего кода будет одинакова:

    Проверено в Excel 2016.

    Вывод сообщений о числовых значениях цветов

    Числовые значения цветов запомнить невозможно, поэтому часто возникает вопрос о том, как узнать числовое значение фона ячейки. Следующий код VBA Excel выводит сообщения о числовых значениях присвоенных ранее цветов.

    Пример кода 2:

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

    Использование предопределенных констант

    В VBA Excel есть предопределенные константы часто используемых цветов для заливки ячеек:

    Предопределенная константа Наименование цвета
    vbBlack Черный
    vbBlue Голубой
    vbCyan Бирюзовый
    vbGreen Зеленый
    vbMagenta Пурпурный
    vbRed Красный
    vbWhite Белый
    vbYellow Желтый
    xlNone Нет заливки

    Присваивается цвет ячейке предопределенной константой в VBA Excel точно так же, как и числовым значением:

    Пример кода 3:

    Цветовая модель RGB

    Цветовая система RGB представляет собой комбинацию различных по интенсивности основных трех цветов: красного, зеленого и синего. Они могут принимать значения от 0 до 255. Если все значения равны 0 — это черный цвет, если все значения равны 255 — это белый цвет.

    Выбрать цвет и узнать его значения RGB можно с помощью палитры Excel:

    Палитра Excel
    Палитра Excel

    Чтобы можно было присвоить ячейке или диапазону цвет с помощью значений RGB, их необходимо перевести в десятичное число, обозначающее цвет. Для этого существует функция VBA Excel, которая так и называется — RGB.

    Пример кода 4:

    Список стандартных цветов с RGB-кодами смотрите в статье: HTML. Коды и названия цветов.

    Очистка ячейки (диапазона) от заливки

    Для очистки ячейки (диапазона) от заливки используется константа xlNone:

    Свойство .Interior.ColorIndex объекта Range

    До появления Excel 2007 существовала только ограниченная палитра для заливки ячеек фоном, состоявшая из 56 цветов, которая сохранилась и в настоящее время. Каждому цвету в этой палитре присвоен индекс от 1 до 56. Присвоить цвет ячейке по индексу или вывести сообщение о нем можно с помощью свойства .Interior.ColorIndex:

    Пример кода 5:

    Просмотреть ограниченную палитру для заливки ячеек фоном можно, запустив в VBA Excel простейший макрос:

    Пример кода 6:

    Номера строк активного листа от 1 до 56 будут соответствовать индексу цвета, а ячейка в первом столбце будет залита соответствующим индексу фоном.

    Подробнее о стандартной палитре Excel смотрите в статье: Стандартная палитра из 56 цветов, а также о том, как добавить узор в ячейку.


    Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.

    86 комментариев для “VBA Excel. Цвет ячейки (заливка, фон)”

    1. Спасибо, наконец то разобрался во всех перипетиях заливки и цвета шрифта.

    2. Евгений

      Пожалуйста, Виктор. Очень рад, что статья пригодилась.

    3. Евгений

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

    4. Подскажите пожалуйста, как можно посчитать количество залитых определенным цветом ячеек в таблице?

    5. Евгений

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

    6. Каким образом можно использовать не в процедуре, а именно в пользовательской функции VBA свойство .Interior.Color?
      Скажем, проверять функцией значение какой-то ячейки и подкрашивать ячейку в зависимости от этого.

    7. Евгений

      Фарин, пользовательская функция VBA предназначена только для возврата вычисленного значения в ячейку, в которой она расположена. Она не позволяет внутри себя менять формат своей ячейки, а также значения и форматы других ячеек.

      Однако, с помощью пользовательской функции VBA можно вывести значения свойств ячейки, в которой она размещена:

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

      Для подкрашивания ячейки в зависимости от ее значения используйте процедуру Sub или штатный инструмент Excel – условное форматирование.

      1. а как можно закрасить только пустые ячейки ?

      2. Евгений

        Лев, закрасить пустые ячейки можно с помощью цикла For Each… Next:

    8. Евгений, спасибо за ссылку на интересный прием.

    9. Александр

      Евгений, день добрый.
      Подскажите пожалуйста, как назначить ячейке цвет через значение RGB, которое в ней записано. Или цвет другой ячейки.

      1. Евгений

        Привет, Александр!
        Используйте функцию InStr, чтобы найти положение разделителей, а дальше функции Left и Mid. Смотрите пример с пробелом в качестве разделителя:

        Или еще проще с помощью функции Split:

    10. Валентина

      Добрый день!
      подскажите, пожалуйста, как можно выводить из таблицы (150 столбцов х 150 строк) адрес ячеек (списком), если они имеют заливку определенного цвета.
      Заранее спасибо!

      1. Евгений

        Привет, Валентина!
        Используйте два цикла For…Next. Определить числовой код цвета можно с помощью выделения одной из ячеек с нужным цветом.

        1. столбец «D» имеет разноцветную заливку
          надо справа от зеленой ячейки написать «Да»

          1. Евгений

            Каким-то образом надо узнать числовое значение цвета, если он не стандартный — vbGreen. Например, можно выделить ячейку с нужным цветом и записать числовое значение цвета в переменную:

            1. Евгений, спасибо за подсказку.
              Все получилось

      1. Валентина

        добрый день! подскажите, пожалуйста, как сделать, чтобы результаты выводились на отдельный лист ?
        заранее спасибо!

      2. Евгений

        Валентина, замените в коде имя «Лист2» на имя своего листа.

    11. Алексей

      Евгений. Долгое время мучаюсь реализацией следующего сценария: в таблице Excel, которая является базой данных пациентов отделения есть столбец «G» в котором лаборанты отмечают исследования выполненные с контрастом «(С+)» и без «(C-)» и далее в столбце «N» они отмечаются количество использованного контраста «от 50мл до 200мл»; для удобства ввода и уменьшения числа непреднамеренных ошибок в столбцах реализована функция проверки данных что бы сотрудники могли выбирать уже готовые значения из списка и если ошибутся то выскочит ошибка; тем не менее сотрудники умудряются при заполнении таблицы не вносить количество использованного контраста. Вопрос заключается в том, как подкрасить ячейку для ввода количества контраста красным цветом при условии, что в ячейке столбца G фигурирует (С+) с целью акцентировать на этом внимание.
      Заранее спасибо за ответ.

      1. Евгений

        Добрый день, Алексей!
        Примените условное форматирование:

        1 Выберите столбец «N».
        2 На вкладке ленты «Главная» перейдите по ссылкам «Условное форматирование» «Создать правило».
        3 В открывшемся окне выберите тип правила: «Использовать формулу для определения форматируемых ячеек».
        4 В строку формул вставьте =И(ЕСЛИ(G1="(C+)";1);ЕСЛИ(N1="";1)). Буква «C» должна быть из одной раскладки (ENG или РУС) в формуле и в ячейке.
        5 Нажмите кнопку «Формат» и на вкладке «Заливка» выберите красный цвет.
        6 Закройте все окна, нажимая «OK».

        Если в ячейке столбца «G» будет выбрано «(С+)», то ячейка той же строки в столбце «N» подкрасится красным цветом. После ввода значения в ячейку столбца «N», ее цвет изменится на первоначальный.

    12. Алексей

      Спасибо Евгений! Ваш пример многое прояснил (в т.ч надо читать Уокенбаха и не филонить). Мне удалось заставить работать этот сценарий не так изящно как у Вас т.е создал для каждой отдельной переменной свое правило: пр. для ГМ. (С+) —> =ЕСЛИ(И(G5066=»ГМ. (С+)»;N5066=»»);»Истина»;»Ложь»)
      МТ. (С+) —> =ЕСЛИ(И(G5066=»МТ. (С+)»;N5066=»»);»Истина»;»Ложь») и т.д всего 8 правил для каждого конкретного случая.
      И применил их всех для столбца N:N

      Ячейку G взял произвольно и в дальнейшем вообще убрал ее на лист метаданных (диапазоны переменных типа ГМ. (С+), МТ. (С+)…)
      Еще раз благодарю за помощь! а есть возможность тоже самое сделать цикличным скриптом VBA ? (или я сморозил…).
      Заранее спасибо за ответ.

      1. Евгений

        Попробуйте так:

        Код размещается в модуле листа. Выбор в столбце 1 значения «(С+)» приводит к окрашиванию ячейки во втором столбце и очистку ее фона после введения в нее любого значения.

    13. Алексей

      Спасибо! Евгений!
      Есть ли у Вас или ваших знакомых-экспертов в программировании практика подрядов на создание баз данных на MsQl с переносом данных из Excel с учетом просьб заказчика и если да то сколько это стоит ?

      1. Евгений

        Алексей, вы можете найти исполнителя на бирже фриланса, создав проект (задание) через безопасную сделку.

    14. Алексей

      Спасибо! уже начал переговоры по проекту.

    15. Здравствуйте.

      Подскажите, пожалуйста, как осуществить следующее:

      Данные в лист будут тянуться из другой таблицы, как сделать так, чтобы в момент появления новой строки проставлялся статус на «Не проверено» в столбце «Статус» и цвет менялся автоматически на серый до проверки и ручного проставления статуса, допустим, «ОК».

      Спасибо!

      1. Евгений

        Привет, Егор!

        Для изменения цвета строки используйте условное форматирование.
        Допустим, столбец с условиями (Статус) — G.
        Тогда, условие: =$G1=»Не проверено»
        Формат: выберите серый цвет.
        Применяется к: выберите весь лист (для Excel 2016: =$1:$1048576).

        Чтобы добавить «Не проверено» в столбец «Статус» используйте формулу: =ЕСЛИ(A2="";"";"Не проверено"). Вставьте ее во вторую ячейку столбца «Статус» и протяните вниз. Столбец «Статус» должен находиться в конце таблицы, чтобы добавляемые строки не перезаписывали формулу. При редактировании ячейки с текстом «Не проверено» формула будет заменена новым текстом и заливка исчезнет.

    16. Евгений, добрый день

      Можно ли реализовать запуск макроса при изменении ЦВЕТА ячейки?

      Спасибо!

      1. Евгений

        Привет, Кирилл!
        В VBA Excel нет события рабочего листа, привязанного к смене цвета ячейки. Но можно проверить, не изменился ли цвет ячейки, пока она была активной, при потере фокуса:

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

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

    17. Здравствуйте, подскажите реализацию сценария:
      необходимо определённую ячейку на листе закрасить цветом, которым закрашена ячейка несколько выше на этом же листе.
      Заранее спасибо.

      1. Евгений

        Привет, Игорь!
        Используйте свойство Range.Offset. Так, например, можно закрасить ячейку D10 цветом ячейки B3:

    Обсуждение закрыто.