VBA Color Index

VBA Color Index

Индекс цвета VBA — это функция, с помощью которой мы можем изменить цвет ячейки или текста, который находится в главном меню в разделе «Шрифт». То же самое можно сделать через VBA Macros. Мы можем изменить цвет текста или фона любой ячейки с помощью VBA Macro.

Пример № 1

С помощью VBA Coding очень легко раскрасить любую ячейку или текст.

Вы можете скачать этот шаблон VBA Color Index Excel здесь — шаблон VBA Color Index Excel

Для этого мы рассмотрели текст «Цвет» в ячейке, как показано ниже.

Теперь, чтобы закрасить выбранную ячейку любого выбора посредством кодирования VBA, нажмите Alt + F11 . Это приведет нас к окну VBA. Теперь вставьте новый модуль. Для этого перейдите в меню «Вставка» и выберите «Модуль», как показано ниже.

После этого мы получим пустой модуль, в который напишем код для Color Index. Для этого создайте тело для кода с подкатегорией . Для лучшего понимания назовите Подкатегорию с функцией использования.

Код:

Сначала выберите диапазон и выберите ячейку, которую нужно раскрасить. Здесь у нас есть ячейка B2 с текстом «Цвет».

Код:

Теперь введите и выберите Интерьер, как показано ниже. У этого есть много свойств, чтобы выбрать.

Код:

После этого введите и выберите Цвет из списка. Это позволит работать с разными цветами.

Код:

В VBA есть только один способ выбрать любой цвет. Введите « VB » перед названием цвета. Мы можем добавить и смешать только 8 основных базовых цветов. Это красный, черный, зеленый, желтый, синий, пурпурный, голубой и белый. Введите vb в начале этих цветов, как показано ниже.

Для завершения введите любой цвет в соответствии с форматом выше. Давайте рассмотрим vbGreen .

Код:

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

Пример № 2

Мы можем изменить цвет любой выбранной ячейки в комбинированной смеси красный-зеленый-синий (или широко известный как RGB). Мы можем решить цветовой оттенок и количество оттенка, который мы хотим дать. Большее значение, придаваемое любому цвету, сделает его темного оттенка.

Для этого мы рассмотрим ту же ячейку с именем «Цвет». Теперь перейдите в окно VBA и откройте новый модуль. А в новом модуле напишите подкатегорию цвета, как показано ниже.

Код:

Выберите диапазон или ячейку с помощью команды Range и выберите ячейку «B2».

Код:

Теперь, как показано в примере-1, выберите Interior.Color, который активирует все применимые цвета для этой выбранной ячейки и, как объяснено, RGB дает любой диапазон цветовых оттенков. Здесь мы дали красный — 200, зеленый — 100 и синий — 150.

Код:

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

Пример № 3

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

Теперь перейдите в VBA и откройте новый модуль. Когда закончите, начните писать подкатегорию, как показано ниже.

Код:

Сначала выберите диапазон и выберите ячейку, текст которой нужно раскрасить. Здесь у нас есть ячейка B2 с текстом «Цвет».

Код:

Теперь, как показано в примере 2, вместо Interior.Color, выберите функцию Font.Color, как показано ниже. Команда Font выберет шрифт и текст выделенной ячейки.

А теперь используйте RGB, чтобы создать нужный цвет и внедрить его в выбранный текст ячейки. Здесь мы дали цветовую гамму Красный — 153, Зеленый — 50 и Синий — 204.

Код:

После этого запустите код с помощью клавиши F5 или вручную.

Мы увидим, что цвет текста в ячейке B2 теперь будет изменен на фиолетовый цветовой оттенок.

Пример № 4

В Microsoft Excel определены все возможные цвета, а также задан цветовой код. В приведенных выше примерах мы использовали команду Interior для изменения цвета любой ячейки. И с помощью RGB мы также выбрали цветовые оттенки. Теперь мы выберем определенный цветовой код и изменим цвет фона выбранной ячейки с помощью Color.Index . Определенные цветовые индексы приведены ниже.

Эти цветовые индексы взяты и доступны на веб-сайте Microsoft. Теперь перейдите в окно VBA и откройте новый модуль. И в новом модуле напишите Подкатегорию.

Код:

Сначала выберите диапазон и выберите ячейку, шрифт которой необходимо раскрасить. Здесь у нас есть ячейка B2 с текстом «Цвет».

Код:

После написания шрифта . Это позволит шрифты преуспеть.

И только после продолжения кодирования напишите и выберите Color.Index и выберите любой код цвета, как показано выше. Здесь мы выбираем индекс цвета 10, который представляет зеленый.

Код:

Теперь запустите код, используя клавишу F5 или вручную. Затем мы увидим, что цвет шрифта выбранной ячейки B2 теперь изменился на зеленый, как показано ниже.

Мы можем сделать это и для цвета фона.

Плюсы VBA Color Index

• Изменение цвета с VBA так же просто, как и изменение функций Excel.
• Любой цвет может быть выбран.

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

• Всегда рекомендуется использовать VBA Color Index, когда используются статистические данные.
• Всегда сохраняйте файл в Macro-Enabled Excel, чтобы созданный код и макрос можно было использовать несколько раз.
• Использование цветового индекса вне списка из 56 цветов невозможно.
• Цветовой индекс можно использовать как для изменения цвета шрифта, так и для фона.

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

Это было руководство к Excel VBAColor Index. Здесь мы обсудили, как использовать Color Index в VBA вместе с некоторыми практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи —

Стандартный фильтр и сортировка по цвету в Excel

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

Добавим фильтр к диапазону с таблицей (выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр или воспользуемся сочетанием клавиш Ctrl + Shift + L), далее щелкаем по стрелке в заголовке столбца и в выпадающем списке можем выбрать любой вариант сортировки или фильтрации:

Из недостатков данного способа фильтрации можно отметить невозможность отфильтровать диапазон по нескольким цветам.

С сортированием подобных проблем не возникает, для этого необходимо последовательно отсортировать данные по заданным цветам.

Например, если мы хотим, чтобы сначала в таблице были показаны ячейки с красной заливкой, а затем с синей, то на первом шаге сделаем сортировку по синей заливке (т.е. сортируем данные в обратном порядке — если в конечном итоге нужен порядок ячеек красный -> синий, то сортируем в порядке синий -> красный):

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

Аналогичного результата также можно добиться отсортировав данные с помощью инструмента Настраиваемая сортировка (также выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр), где можно настроить различные дополнительные параметры и уровни сортировки:

Изменение цвета текста в ячейке или диапазоне

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

На вкладке Главная щелкните стрелку рядом с кнопкой Цвет шрифта .

Выберите цвет в группе Цвета темы или Стандартные цвета.

Совет: Чтобы применить последний выбранный цвет текста, на вкладке Главная нажмите кнопку Цвет текста.

Условное форматирование в диаграммах Excel

Изучая статистику по коронавирусу (актуальному на момент написания статьи), я зашел на информационную страницу Яндекса с данными по заболеваниям и выздоровлениям и обнаружил там довольно интересную диаграмму:

Заинтересовало меня то, что цвет столбца изменяется в зависимости от соответствующего значения. И чем больше это значение, тем сильнее «краснеет» цвет столбца диаграммы. То есть, грубо говоря, это — условное форматирование столбцов диаграммы, где условие — принадлежность числового значения самого столбца к некоторому диапазону.

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

Условное форматирование с помощью дополнительных столбцов.

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

На словах, возможно, звучит немного запутанно, но сейчас все покажу на примере.

Зеленым выделена наша основная таблица с данными. Справа от нее — 4 столбца, по которым распределяются исходные значения, в зависимости от попадания в определенный диапазон:

• Значения менее 3000. Формула в ячейке C2: =ЕСЛИ( B2 <3000; B2 ;НД () )
• Значения более 3000, но менее 5000. Формула в ячейке D2: =ЕСЛИ(И( B2 >=3000; B2 <5000);B2;НД () )
• Значения более 5000, но менее 7000. Формула в ячейке E2: =ЕСЛИ(И( B2 >=5000; B2 <7000);B2;НД())
• Значения более 7000. Формула в ячейке F2: =ЕСЛИ( B2 >=7000; B2 ;НД () )

Если значение не попадает в какой-либо диапазон, то в соответствующем столбце выводится ошибка #Н/Д. Это нужно для того, чтобы «неправильные» значения не отображались на диаграмме. Если вам мешаются ячейки с #Н/Д, то могу предложить несколько вариантов:

1. Включить классическое условное форматирование для ячеек, которое будет изменять цвет шрифта ячеек с ошибками на белый. Таблица будет выглядеть опрятнее.
2. После построения диаграммы, скрыть столбцы с дополнительной частью таблицы. Изначально, диаграмма не будет отображать скрытые данные, но это решается установкой галочки «Показывать данные в скрытых строках и столбцах» в настройках (при выборе источника данных для диаграммы).

После этого, вставляем новую диаграмму и источником выбираем всю нашу большую таблицу, исключая начальный столбец со значениями (чтобы не было задвоения данных на диаграмме). После этого, как я уже говорил, устанавливаем «Перекрытие столбцов» на 100% и получаем уже готовую таблицу, в которой уже реализовано условное форматирование.

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

Условное форматирование диаграммы с помощью VBA.

Второй вариант — использование макросов VBA. Нравится этот способ мне гораздо больше: не нужно строить лишние таблицы, выбирать новые источники данных в настройках и настраивать «корректный» вывод ошибок с «#Н/Д». Достаточно один раз подготовить код и использовать его по необходимости.

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

Все просто. Данный макрос подсвечивает диаграмму тремя цветами (по типу «Светофор»: красный, желтый и зеленый), в зависимости от принадлежности значения столбца диаграммы к определенному диапазону. Диапазонов, соответственно, тоже три и задаются они с помощью двух переменных: FirstValue и SecondValue (все значения меньше FirstValue, между FirstValue и SecondValue и больше SecondValue). Значения этих переменных задаются в макросе, точно так же, как и цвета.

При запуске данного макроса мы получим следующий результат:

Все столбцы со значением менее 700000 были залиты красным цветом, со значением более 900000 — зеленым, а в диапазоне от 700000 до 900000 — желтым.

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

• Добавить еще несколько условий
• Добавить под новые условия новые цвета
• Создать форму VBA, на которой можно будет самостоятельно выбирать цвета через палитру и задавать диапазоны условий, не изменяя код

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

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

Надстройка SHTEM для Excel.

Условное форматирование для диаграммы реализовано пока что только в тестовой версии надстройки SHTEM для Excel: с формой для ввода ограничений и несколькими заданными наборами цветов (в том числе и с градиентом). Сейчас функция тестируется на работоспособность в различных условиях и спустя некоторое время будет добавлена в основную версию надстройки.

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

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

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

Условное форматирование: заключение.

Если Вы часто формируете диаграммы, где столбцы должны быть залиты разными цветами, в зависимости от их значения — перечисленные способы безусловно вам подойдут. «Гибкими» их, конечно, не назовешь, но в любом случае, это лучше, чем ручная заливка каждого из столбцов. Если Вам известны какие-нибудь другие способы условного форматирования диаграммы или просто хотите дополнить мою статью — пишите об этом в комментариях, обязательно все прочитаю. А скачать файл с реализацией перечисленных здесь способов заливки диаграммы, можно нажав на кнопочку ниже:

Символы

Таблица символов в Excel

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

• В нижней части окна выберите стиль.
• В таблице выберите подходящий символ.
• Для первого параметра «ЕСЛИ ЗНАЧЕНИЕ:» установите «>». Остальное можно оставить без изменений. Нажатие на «ОК» сохраняет правило Excel.

Как сделать разделение в Excel

Границы в программе могут быть добавлены к верхней, нижней, правой и левой части ячейки. Кроме того, они могут иметь орнамент и цвет на выбор. Это позволяет вам более тонко настраивать её отображение при создании нужной таблицы. Так, можно настроить разделение для одного или нескольких объектов. Например, можно отделить в файле целую строку, столбец или сектор, либо разлиновать всю таблицу.

Существует 2 способа создания разделения в файле Эксель:

1. Через меню «Границы» во вкладке «Главная»;
2. Кликнув на «Другие границы» в этом же меню.

Оба варианта имеют свои особенности и преимущества, но любой из них позволит создать и изменить разделение.

Создание контура через кнопку «Границы»

Этот вариант — самый простой из всех, но и предоставляет он меньше всего возможностей. Выделите определённое количество ячеек, к которым хотите применить настройки, после чего:

1. Нажмите на кнопку «Границы», которая расположена снизу от выбора шрифта (её внешний вид зависит от последней использованной функции, но по умолчанию это «нижняя граница»);
2. Укажите необходимую команду, после чего разметка будет применена;
3. Сохраните прогресс.

Создание разметки через «Другие границы»

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

1. Откройте меню «Границы»;
2. Кликните на «Другие границы» внизу выпадающего окошка — откроется вкладка, в которой вы сможете самостоятельно нарисовать контур любой сложности, какой позволяет программа;
3. Сделайте шаблон, нажмите «ОК»;
4. Сохраните прогресс.

Вкладка «Границы» позволяет сделать разметку более сложной. Вам доступны 13 видов контура и все цвета спектра, а также возможность нарисовать внешние и внутренние линии объекта (в том числе диагональные).

Как нарисовать разметку

Для того чтобы создать собственный контур, нужно:

1. Указать элемент, либо определённое количество групп;
2. В выпадающем меню «Границы» кликнуть на последнюю вкладку;
3. Откроется окно «Формат ячеек» со вкладкой «Границы» — выберите тип линии, её цвет, расположение относительно элемента;
   
4. Укажите контур, нажимая на кнопки с соответствующими изображениями, либо на саму модель элемента;
5. Если в базовой палитре нет подходящего цвета, откройте «Другие цвета» (переход во вкладку «Спектр» в новом окошке позволит сделать настройку ещё более точно);
   
6. Если вам не нужны линии, выберите «Нет» в окне «Формат ячеек»;
7. Нажмите «ОК» и сохраните прогресс.

Как сохранить разметку при печати

По умолчанию, как серые, базовые линии на поверхности листа Excel, так и любые другие, не печатаются при выводе на бумагу через принтер. Это можно заметить, нажав «Печать» — «Предварительный просмотр». Для того чтобы включить контур в будущий документ, необходимо указать определённое количество листов и применить соответствующую функцию. Варианты выделения:

• Отдельный лист — просто щёлкните на него. Если он не виден в списке, сначала прокрутите с помощью кнопки.
• Два или несколько листов, идущих рядом — кликните на ярлык крайнего в диапазоне (слева или справа), после чего, зажав Shift, нажмите на противоположный ему (с другой стороны множества).
• Два и более несмежных — кликните на ярлык первого листа. Затем, удерживая Ctrl, нажмите на остальные, которые нужно включить в диапазон.
• Все страницы в файле Эксель — кликните на ярлык любого правой клавишей мыши, после чего в меню выберите «Выделить все листы».

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

Дальнейшие действия для печати:

1. На вкладке «Разметка страницы» в «Параметры страницы» выберите пункт «Печать» в области «Сетка»;
2. Откройте «Файл», нажмите «Печать» (или Ctrl + «P»);
3. В окне с параметрами печати нажмите «ОК».

Если вы не можете выбрать пункт в области «Сетка», значит, выбран график или элемент в таблице. Для того чтобы функции стали доступны, необходимо снять это выделение. Если при печати контур вам не нужен, соответственно, снимите галочку с пункта «Печать» в «Сетка».

Как удалить условное форматирование

Для удаления формата проделайте следующие действия:

• Выделите ячейки;
• Нажмите на пункт меню “Условное форматирование” на панели инструментов. Кликните по пункту “Удалить правила”. В раскрывающемся меню выберите метод удаления:

Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе “От новичка до мастера Excel“. Успей зарегистрироваться по ссылке!

Спасибо, очень полезный сайт!
Вопрос:
Есть таблица с остатками на складе. Последний столбец “остаток” – это формула “приход” минус “выдали”.
Пытаюсь по вашей статье создать правило автоматической окраски строки со значением “0” в ячейке “остаток”. Но при создании условного форматирования выдаёт ошибку – ячейка уже содержит формулу, а не просто число. К тому же окрашиваются только ячейки с нужным значением, а не вся строка. Есть способ решить такую проблему?