Vba excel закрасить ячейку
Когда мы из кода VBA Excel записываем в ячейку текстовое или другое значение, оно отображается в формате, присвоенном данной ячейке. Это может быть формат:
- рабочего листа по умолчанию;
- установленный для диапазона пользователем;
- примененный к диапазону из кода VBA Excel.
Если ячейка содержит текстовое значение, его начертание можно форматировать по отдельным частям (подстрокам). Такое форматирование доступно как в ручном режиме на рабочем листе, так и из кода VBA Excel.
У объекта Range есть свойство Font (шрифт), которое отвечает за форматирование (начертание) визуально отображаемого текста в ячейках рабочего листа. Его применение вызывает объект Font, который в свою очередь обладает собственным набором свойств, отвечающих за конкретный стиль начертания отображаемого значения.
Применение стилей к ячейкам листа
В Excel изначально установлено множество встроенных стилей. Найти их можно в меню Стили ячеек, которая расположена на вкладке Главная -> Стили.
Откроется галерея стилей (рисунок справа).
Чтобы применить стиль к выделенной ячейке или диапазону, необходимо щелкнуть левой кнопкой мыши по нужному стилю. Имеется также очень удобная возможность предварительного просмотра: при наведении курсора на стиль, Вы будете видеть как меняется стиль ячейки.
После применения стиля из галереи можно будет накладывать дополнительное форматирование на ячейки.
VBA-макрос: заливка, шрифт, линии границ, ширина столбцов и высота строк
В процессе запыления данных сотрудниками отдела на некоторых листах были изменены форматы ячеек:
Необходимо сбросить форматирование ячеек и сделать так чтобы на всех таблицах планов выполнения работ были одинаковые форматы отображения данных. Формат ячеек для исходной таблицы должен быть закреплен за шаблоном, чтобы можно было сделать сброс и применять заданный стиль оформления в дальнейшем.
Чтобы выполнять такие задачи вручную можно попытаться облегчить процесс настройки множества опций форматирования для многих диапазонов ячеек на разных листах и рабочих книгах. Плюс к о всему можно ошибиться и применить несколько другие настройки форматирования.
Макросы Excel прекрасно справляются с форматированием ячеек на рабочих листах. Кроме того, делают это быстро и в полностью автоматическом режиме. Воспользуемся этими преимуществами и для решения данной задачи напишем свой код VBA-макроса. Он поможет нам быстро и безопасно сбрасывать форматы на исходный предварительно заданный в шаблоне главной таблицы.
Чтобы написать свой код макроса откройте специальный VBA-редактор в Excel: «РАЗРАБОТЧИК»-«Код»-«Visual Basic» или нажмите комбинацию клавиш ALT+F11:
В редакторе создайте новый модуль выбрав инструмент «Insert»-«Module» и введите в него такой VBA-код макроса:
Sub SbrosFormat()
If TypeName(Selection) <> "Range" Then Exit Sub
With Selection
.HorizontalAlignment = xlVAlignCenter
.VerticalAlignment = xlVAlignCenter
.WrapText = True
.Borders.LineStyle = xlContinuous
.Borders.Weight = xlThin
.Font.ColorIndex = xlColorIndexAutomatic
.Interior.ColorIndex = xlColorIndexAutomatic
.Columns.AutoFit
.Rows.AutoFit
End With
End Sub
Теперь если нам нужно сбросить форматирование таблицы на исходный формат отображения ее данных, выделите диапазон ячеек A1:E20 и запустите макрос: «РАЗРАБОЧТИК»-«Код»-«Макросы»-«SbrosFormat»-«Выполнить». Результат работы макроса изображен ниже на рисунке:
Таблица приобрела формат, который определен макросом. Таким образом код VBA нам позволяет сбросить любые изменения формата ячеек на предустановленный автором отчета.
Пример кода 2:
Выравнивание по горизонтали
Для выравнивания текста в ячейках рабочего листа по горизонтали в VBA Excel используется свойство HorizontalAlignment объекта Range. Оно может принимать следующие значения:
Выравнивание | Константа | Значение |
По левому краю | xlLeft | -4131 |
По центру | xlCenter | -4108 |
По правому краю | xlRight | -4152 |
Константу использовать удобнее, так как ее можно выбрать из подсказок и легче запомнить. Но и присвоение свойству HorizontalAlignment непосредственно числового значения константы будет работать точно так же.
Пример 1
Заполним три первые ячейки листа Excel текстом, соответствующим предполагаемому выравниванию. Затем применим к ним выравнивание по горизонтали, а в ячейках ниже выведем соответствующие значения констант.
Основные свойства объекта Font
Свойство | Описание | Значения |
Name | наименование шрифта | “Arial”, “Calibri”, “Courier New”, “Times New Roman” и т.д. |
Size | размер шрифта | от 1 до 409 пунктов |
Bold | полужирное начертание | True, False |
Italic | курсивное начертание | True, False |
FontStyle | заменяет Bold и Italic | “обычный”, “полужирный”, “курсив”, “полужирный курсив” |
Superscript | надстрочный текст | True, False |
Subscript | подстрочный текст | True, False |
Underline | подчеркнутый текст | True, False |
Color* | цвет текста | от 0 до 16777215 |
*Color – это не единственное свойство, отвечающее за цвет отображаемого текста в ячейке. Оно также может принимать и другие значения, кроме указанных в таблице. Смотрите подробности в статье Цвет текста (шрифта) в ячейке .
Синтаксис и параметры
Format(Expression, [FormatExpression], [FirstDayOfWeek], [FirstWeekOfYear])
- Expression – любое допустимое выражение (переменная), возвращающее числовое значение или строку (обязательный параметр).
- FormatExpression – выражение формата, именованное или содержащее инструкции из специальных символов (необязательный параметр).
- FirstDayOfWeek – константа, задающая первый день недели (необязательный параметр).
- FirstWeekOfYear – константа, задающая первую неделю года (необязательный параметр).
Именные форматы даты и времени
Имя формата | Описание |
---|---|
General Date | Стандартное отображение даты и времени в соответствии с параметрами системы. |
Long Date | Длинный формат даты. |
Medium Date | Средний формат даты. |
Short Date | Краткий формат даты. |
Long Time | Длинный формат времени. |
Medium Time | Средний формат времени. |
Short Time | Краткий формат времени. |
Проверьте отображение даты и времени с использованием именованных форматов на вашем компьютере при помощи следующего кода VBA Excel:
Скорее всего, результат будет таким:
Именованные форматы чисел
Имя формата | Описание |
---|---|
General Number | Стандартное отображение числа без знака разделителя групп разрядов. |
Currency | Денежный формат. |
Fixed | Отображение числа без знака разделителя групп разрядов с двумя цифрами после разделителя целой и дробной части. |
Standard | Отображение числа со знаком разделителя групп разрядов и с двумя цифрами после разделителя целой и дробной части. |
Percent | Процентный формат: отображение числа, умноженного на 100, со знаком процента (%), добавленного справа. |
Scientific | Отображение числа в экспоненциальном виде. |
Yes/No | Возвращается «Нет», если число равно 0, иначе отображается «Да». |
True/False | Возвращается «Ложь», если число равно 0, иначе отображается «Истина». |
On/Off | Возвращается «Выкл», если число равно 0, иначе отображается «Вкл». |
Проверяем работу именованных форматов на числах 2641387.7381962 и 0 с помощью кода VBA Excel:
Получаем следующий результат:
Вместо вопросительного знака в отображении числа в формате Currency, по идее, должен быть знак валюты (₽ или руб.).
Символы для форматов даты и времени
Символ | Описание |
---|---|
Точка (.) | Разделитель компонентов даты (день, месяц, год). Используется при отображении месяца в виде числа. |
Пробел | Разделитель компонентов даты (день, месяц, год). Используется при отображении месяца прописью. |
Двоеточие (:) | Разделитель компонентов времени (часы, минуты, секунды). |
d | День в виде числа без нуля в начале (1–31). |
dd | День в виде числа с нулем в начале (01–31). |
m | Месяц в виде числа без нуля в начале (1–31). Если (m) следует после (h) или (hh), отображаются минуты. |
mm | Месяц в виде числа с нулем в начале (01–31). Если (mm) следует после (h) или (hh), отображаются минуты. |
mmm | Месяц прописью в сокращенном виде (янв–дек). |
mmmm | Полное название месяца (январь–декабрь). |
y | День года в виде числа (1–366). |
yy | Год в виде 2-значного числа (00–99). |
yyyy | Год в виде 4-значного числа (1900–9999). |
h | Часы в виде числа без нуля в начале (0–23). |
hh | Часы в виде числа с нулем в начале (00–23). |
n (m) | Минуты в виде числа без нуля в начале (0–59). |
nn (mm) | Минуты в виде числа с нулем в начале (00–59). |
s | Секунды в виде числа без нуля в начале (0–59). |
ss | Секунды в виде числа с нулем в начале (00–59). |
Примеры отображения даты с помощью разных по количеству наборов символа d:
Заливка ячейки цветом в VBA Excel
Пример кода 1:
Поместите пример кода в свой программный модуль и нажмите кнопку на панели инструментов «Run Sub» или на клавиатуре «F5», курсор должен быть внутри выполняемой программы. На активном листе Excel ячейки и диапазон, выбранные в коде, окрасятся в соответствующие цвета.
Есть один интересный нюанс: если присвоить свойству .Interior.Color отрицательное значение от -16777215 до -1, то цвет будет соответствовать значению, равному сумме максимального значения палитры (16777215) и присвоенного отрицательного значения. Например, заливка всех трех ячеек после выполнения следующего кода будет одинакова:
Проверено в Excel 2016.
Создание новых стилей
Если встроенных стилей Excel не достаточно, то можно создать собственные стили. Это достаточно просто сделать:
В результате в активную книгу будет добавлен новый пользовательский стиль, который будет доступен в меню Стили ячеек.
Выравнивание по вертикали
Для выравнивания текста в ячейках рабочего листа по вертикали в VBA Excel используется свойство VerticalAlignment объекта Range. Оно может принимать следующие значения:
Выравнивание | Константа | Значение |
По верхнему краю | xlTop | -4160 |
По центру | xlCenter | -4108 |
По нижнему краю | xlBottom | -4107 |
Точно так же, как и по горизонтали, при выравнивании по вертикали свойству VerticalAlignment можно присваивать как значение из константы, так и непосредственно ее числовое значение.
Пример 2
Заполним три первые ячейки третьей строки листа Excel текстом, соответствующим предполагаемому выравниванию. Затем применим к ним выравнивание по вертикали, а в ячейках ниже выведем соответствующие значения констант.
Объект Range в VBA Excel представляет диапазон ячеек. Он (объект Range) может описывать любой диапазон, начиная от одной ячейки и заканчивая сразу всеми ячейками рабочего листа.
- Одна ячейка – Range("A1") .
- Девять ячеек – Range("A1:С3") .
- Весь рабочий лист в Excel 2016 – Range("1:1048576") .
Для справки: выражение Range("1:1048576") описывает диапазон с 1 по 1048576 строку, где число 1048576 – это номер последней строки на рабочем листе Excel 2016.
В VBA Excel есть свойство Cells объекта Range, которое позволяет обратиться к одной ячейке в указанном диапазоне (возвращает объект Range в виде одной ячейки). Если в коде используется свойство Cells без указания диапазона, значит оно относится ко всему диапазону активного рабочего листа.
Примеры обращения к одной ячейке:
- Cells(1000) , где 1000 – порядковый номер ячейки на рабочем листе, возвращает ячейку «ALL1».
- Cells(50, 20) , где 50 – номер строки рабочего листа, а 20 – номер столбца, возвращает ячейку «T50».
- Range("A1:C3").Cells(6) , где «A1:C3» – заданный диапазон, а 6 – порядковый номер ячейки в этом диапазоне, возвращает ячейку «C2».
Для справки: порядковый номер ячейки в диапазоне считается построчно слева направо с перемещением к следующей строке сверху вниз.
Подробнее о том, как обратиться к ячейке, смотрите в статье: Ячейки (обращение, запись, чтение, очистка).
В этой статье мы рассмотрим свойства объекта Range, применимые, в том числе, к диапазону, состоящему из одной ячейки.
Еще надо добавить, что свойства и методы объектов отделяются от объектов точкой, как в третьем примере обращения к одной ячейке: Range("A1:C3").Cells(6) .
Свойства ячейки (объекта Range)
Свойство | Описание |
---|---|
Address | Возвращает адрес ячейки (диапазона). |
Borders | Возвращает коллекцию Borders, представляющую границы ячейки (диапазона). Подробнее… |
Cells | Возвращает объект Range, представляющий коллекцию всех ячеек заданного диапазона. Указав номер строки и номер столбца или порядковый номер ячейки в диапазоне, мы получаем конкретную ячейку. Подробнее… |
Characters | Возвращает подстроку в размере указанного количества символов из текста, содержащегося в ячейке. Подробнее… |
Column | Возвращает номер столбца ячейки (первого столбца диапазона). Подробнее… |
ColumnWidth | Возвращает или задает ширину ячейки в пунктах (ширину всех столбцов в указанном диапазоне). |
Comment | Возвращает комментарий, связанный с ячейкой (с левой верхней ячейкой диапазона). |
CurrentRegion | Возвращает прямоугольный диапазон, ограниченный пустыми строками и столбцами. Очень полезное свойство для возвращения рабочей таблицы, а также определения номера последней заполненной строки. |
EntireColumn | Возвращает весь столбец (столбцы), в котором содержится ячейка (диапазон). Диапазон может содержаться и в одном столбце, например, Range("A1:A20") . |
EntireRow | Возвращает всю строку (строки), в которой содержится ячейка (диапазон). Диапазон может содержаться и в одной строке, например, Range("A2:H2") . |
Font | Возвращает объект Font, представляющий шрифт указанного объекта. Подробнее о цвете шрифта… | HorizontalAlignment | Возвращает или задает значение горизонтального выравнивания содержимого ячейки (диапазона). Подробнее… |
Interior | Возвращает объект Interior, представляющий внутреннюю область ячейки (диапазона). Применяется, главным образом, для возвращения или назначения цвета заливки (фона) ячейки (диапазона). Подробнее… |
Name | Возвращает или задает имя ячейки (диапазона). |
NumberFormat | Возвращает или задает код числового формата для ячейки (диапазона). Примеры кодов числовых форматов можно посмотреть, открыв для любой ячейки на рабочем листе Excel диалоговое окно «Формат ячеек», на вкладке «(все форматы)». Свойство NumberFormat диапазона возвращает значение NULL, за исключением тех случаев, когда все ячейки в диапазоне имеют одинаковый числовой формат. Если нужно присвоить ячейке текстовый формат, записывается так: Range("A1").NumberFormat = "@" . |
Offset | Возвращает объект Range, смещенный относительно первоначального диапазона на указанное количество строк и столбцов. Подробнее… |
Resize | Изменяет размер первоначального диапазона до указанного количества строк и столбцов. Строки добавляются или удаляются снизу, столбцы – справа. Подробнее… |
Row | Возвращает номер строки ячейки (первой строки диапазона). Подробнее… |
RowHeight | Возвращает или задает высоту ячейки в пунктах (высоту всех строк в указанном диапазоне). |
Text | Возвращает форматированный текст, содержащийся в ячейке. Свойство Text диапазона возвращает значение NULL, за исключением тех случаев, когда все ячейки в диапазоне имеют одинаковое содержимое и один формат. Предназначено только для чтения. |
Value | Возвращает или задает значение ячейки, в том числе с отображением значений в формате Currency и Date. Тип данных Variant. Value является свойством ячейки по умолчанию, поэтому в коде его можно не указывать. |
Value2 | Возвращает или задает значение ячейки. Тип данных Variant. Значения в формате Currency и Date будут отображены в виде чисел с типом данных Double. | VerticalAlignment | Возвращает или задает значение вертикального выравнивания содержимого ячейки (диапазона). Подробнее… |
В таблице представлены не все свойства объекта Range. С полным списком вы можете ознакомиться не сайте разработчика.
Простые примеры для начинающих
Вы можете скопировать примеры кода VBA Excel в стандартный модуль и запустить их на выполнение. Как создать стандартный модуль и запустить процедуру на выполнение, смотрите в статье VBA Excel. Начинаем программировать с нуля.
Учтите, что в одном программном модуле у всех процедур должны быть разные имена. Если вы уже копировали в модуль подпрограммы с именами Primer1, Primer2 и т.д., удалите их или создайте еще один стандартный модуль.
Форматирование ячеек
Заливка ячейки фоном, изменение высоты строки, запись в ячейки текста, автоподбор ширины столбца, выравнивание текста в ячейке и выделение его цветом, добавление границ к ячейкам, очистка содержимого и форматирования ячеек.
Если вы запустите эту процедуру, информационное окно MsgBox будет прерывать выполнение программы и сообщать о том, что произойдет дальше, после его закрытия.
Начиная с Excel 2007 основным способом заливки диапазона или отдельной ячейки цветом (зарисовки, добавления, изменения фона) является использование свойства .Interior.Color объекта Range путем присваивания ему значения цвета в виде десятичного числа от 0 до 16777215 (всего 16777216 цветов).
Заливка ячейки цветом в VBA Excel
Пример кода 1:
Поместите пример кода в свой программный модуль и нажмите кнопку на панели инструментов «Run Sub» или на клавиатуре «F5», курсор должен быть внутри выполняемой программы. На активном листе Excel ячейки и диапазон, выбранные в коде, окрасятся в соответствующие цвета.
Есть один интересный нюанс: если присвоить свойству .Interior.Color отрицательное значение от -16777215 до -1, то цвет будет соответствовать значению, равному сумме максимального значения палитры (16777215) и присвоенного отрицательного значения. Например, заливка всех трех ячеек после выполнения следующего кода будет одинакова:
Проверено в Excel 2016.
Пример кода 2:
Использование предопределенных констант
В VBA Excel есть предопределенные константы часто используемых цветов для заливки ячеек:
Предопределенная константа | Наименование цвета |
---|---|
vbBlack | Черный |
vbBlue | Голубой |
vbCyan | Бирюзовый |
vbGreen | Зеленый |
vbMagenta | Пурпурный |
vbRed | Красный |
vbWhite | Белый |
vbYellow | Желтый |
xlNone | Нет заливки |
Присваивается цвет ячейке предопределенной константой в VBA Excel точно так же, как и числовым значением:
Пример кода 3:
Цветовая модель RGB
Цветовая система RGB представляет собой комбинацию различных по интенсивности основных трех цветов: красного, зеленого и синего. Они могут принимать значения от 0 до 255. Если все значения равны 0 — это черный цвет, если все значения равны 255 — это белый цвет.
Выбрать цвет и узнать его значения RGB можно с помощью палитры Excel:
Чтобы можно было присвоить ячейке или диапазону цвет с помощью значений RGB, их необходимо перевести в десятичное число, обозначающее цвет. Для этого существует функция VBA Excel, которая так и называется — RGB.
Пример кода 4:
Список стандартных цветов с RGB-кодами смотрите в статье: HTML. Коды и названия цветов.
Очистка ячейки (диапазона) от заливки
Для очистки ячейки (диапазона) от заливки используется константа xlNone :
Свойство .Interior.ColorIndex объекта Range
Пример кода 5:
Просмотреть ограниченную палитру для заливки ячеек фоном можно, запустив в VBA Excel простейший макрос:
Пример кода 6:
Номера строк активного листа от 1 до 56 будут соответствовать индексу цвета, а ячейка в первом столбце будет залита соответствующим индексу фоном.
Готовую стандартную палитру из 56 цветов можете посмотреть здесь.
63 комментария для “VBA Excel. Цвет ячейки (заливка, фон)”
Спасибо, наконец то разобрался во всех перипетиях заливки и цвета шрифта.
Пожалуйста, Виктор. Очень рад, что статья пригодилась.
как проверить наличие фона?
Привет, Надежда!
Фон у ячейки есть всегда, по умолчанию — белый. Отсутствие цветного фона можно определить, проверив, является ли цвет ячейки белым:
Подскажите пожалуйста, как можно посчитать количество залитых определенным цветом ячеек в таблице?
Привет, Иван!
Посчитать ячейки с одинаковым фоном можно с помощью цикла.
Для реализации этого примера сначала выбираем в таблице ячейку с нужным цветом заливки. Затем запускаем код, который определяет цветовой индекс фона активной ячейки, диапазон таблицы вокруг нее и общее количество ячеек с такой заливкой в таблице.
Каким образом можно использовать не в процедуре, а именно в пользовательской функции VBA свойство .Interior.Color?
Скажем, проверять функцией значение какой-то ячейки и подкрашивать ячейку в зависимости от этого.
Фарин, пользовательская функция VBA предназначена только для возврата вычисленного значения в ячейку, в которой она расположена. Она не позволяет внутри себя менять формат своей ячейки, а также значения и форматы других ячеек.
Однако, с помощью пользовательской функции VBA можно вывести значения свойств ячейки, в которой она размещена:
В сети есть эксперименты по изменению значений других ячеек из пользовательской функции VBA, но они могут работать нестабильно и приводить к ошибкам.
Для подкрашивания ячейки в зависимости от ее значения используйте процедуру Sub или штатный инструмент Excel – условное форматирование.
а как можно закрасить только пустые ячейки ?
Лев, закрасить пустые ячейки можно с помощью цикла For Each… Next:
Евгений, спасибо за ссылку на интересный прием.
Евгений, день добрый.
Подскажите пожалуйста, как назначить ячейке цвет через значение RGB, которое в ней записано. Или цвет другой ячейки.
Привет, Александр!
Используйте функцию InStr, чтобы найти положение разделителей, а дальше функции Left и Mid. Смотрите пример с пробелом в качестве разделителя:
Или еще проще с помощью функции Split:
Добрый день!
подскажите, пожалуйста, как можно выводить из таблицы (150 столбцов х 150 строк) адрес ячеек (списком), если они имеют заливку определенного цвета.
Заранее спасибо!
Привет, Валентина!
Используйте два цикла For…Next. Определить числовой код цвета можно с помощью выделения одной из ячеек с нужным цветом.
столбец «D» имеет разноцветную заливку
надо справа от зеленой ячейки написать «Да»
Каким-то образом надо узнать числовое значение цвета, если он не стандартный — vbGreen. Например, можно выделить ячейку с нужным цветом и записать числовое значение цвета в переменную:
Евгений, спасибо за подсказку.
Все получилось
добрый день! подскажите, пожалуйста, как сделать, чтобы результаты выводились на отдельный лист ?
заранее спасибо!
Валентина, замените в коде имя «Лист2» на имя своего листа.
Евгений. Долгое время мучаюсь реализацией следующего сценария: в таблице Excel, которая является базой данных пациентов отделения есть столбец «G» в котором лаборанты отмечают исследования выполненные с контрастом «(С+)» и без «(C-)» и далее в столбце «N» они отмечаются количество использованного контраста «от 50мл до 200мл»; для удобства ввода и уменьшения числа непреднамеренных ошибок в столбцах реализована функция проверки данных что бы сотрудники могли выбирать уже готовые значения из списка и если ошибутся то выскочит ошибка; тем не менее сотрудники умудряются при заполнении таблицы не вносить количество использованного контраста. Вопрос заключается в том, как подкрасить ячейку для ввода количества контраста красным цветом при условии, что в ячейке столбца G фигурирует (С+) с целью акцентировать на этом внимание.
Заранее спасибо за ответ.
Добрый день, Алексей!
Примените условное форматирование:
Если в ячейке столбца «G» будет выбрано «(С+)», то ячейка той же строки в столбце «N» подкрасится красным цветом. После ввода значения в ячейку столбца «N», ее цвет изменится на первоначальный.
Спасибо Евгений! Ваш пример многое прояснил (в т.ч надо читать Уокенбаха и не филонить). Мне удалось заставить работать этот сценарий не так изящно как у Вас т.е создал для каждой отдельной переменной свое правило: пр. для ГМ. (С+) —> =ЕСЛИ(И(G5066=»ГМ. (С+)»;N5066=»»);»Истина»;»Ложь»)
МТ. (С+) —> =ЕСЛИ(И(G5066=»МТ. (С+)»;N5066=»»);»Истина»;»Ложь») и т.д всего 8 правил для каждого конкретного случая.
И применил их всех для столбца N:N
Ячейку G взял произвольно и в дальнейшем вообще убрал ее на лист метаданных (диапазоны переменных типа ГМ. (С+), МТ. (С+)…)
Еще раз благодарю за помощь! а есть возможность тоже самое сделать цикличным скриптом VBA ? (или я сморозил…).
Заранее спасибо за ответ.
Использование цветовой палитры для присвоения цвета тексту в ячейке листа Excel аналогично присвоению цвета фону ячейки, только свойство диапазона .Interior меняем на свойство .Font.
Цвет текста и предопределенные константы
Цвет шрифту в ячейке можно присвоить с помощью предопределенных констант:
Напомню, что вместо индексов строк и столбцов можно использовать переменные. Список предопределенных констант смотрите здесь.
Цвет шрифта и модель RGB
Для изменения цвета текста в ячейке можно использовать цветовую модель RGB:
Аргументы функции RGB могут принимать значения от 0 до 255. Если все аргументы равны 0, цвет — черный, если все аргументы равны 255, цвет — белый. Функция RGB преобразует числовые значения основных цветов (красного, зеленого и синего) в индекс основной палитры.
Список стандартных цветов с RGB-кодами смотрите в статье: HTML. Коды и названия цветов.
Свойство .Font.ColorIndex
Свойство .Font.ColorIndex может принимать значения от 1 до 56. Это стандартная ограниченная палитра, которая существовала до Excel 2007 и используется до сих пор. Посмотрите примеры:
Таблица соответствия значений ограниченной палитры цвету:
Стандартная палитра Excel из 56 цветов
Свойство .Font.ThemeColor
Свойство .Font.ThemeColor может принимать числовые или текстовые значения констант из коллекции MsoThemeColorIndex:
Основная палитра
Основная палитра, начиная c Excel 2007, состоит из 16777216 цветов. Свойство .Font.Color может принимать значения от 0 до 16777215, причем 0 соответствует черному цвету, а 16777215 — белому.
Отрицательные значения свойства .Font.Color
При записи в Excel макрорекордером макроса с присвоением шрифту цвета используются отрицательные значения свойства .Font.Color, которые могут быть в пределах от -16777215 до -1. Отрицательные значения соответствуют по цвету положительному значению, равному сумме наибольшего индекса основной палитры и данного отрицательного значения. Например, отрицательное значение -8257985 соответствует положительному значению 8519230, являющегося результатом выражения 16777215 + (-8257985). Цвета текста двух ячеек из следующего кода будут одинаковы:
Как в Excel динамически изменять цвет ячейки, основываясь на её значении
Цвет заливки будет изменяться в зависимости от значения ячейки.
Задача: Имеется таблица или диапазон данных, и Вы хотите изменить цвет заливки ячеек, основываясь на их значениях. Более того, необходимо, чтобы этот цвет изменялся динамически, отражая изменения данных в ячейках.
Решение: Используйте условное форматирование в Excel, чтобы выделить значения больше X, меньше Y или между X и Y.
Предположим, есть список цен на бензин в разных штатах, и Вы хотите, чтобы цены, превышающие $3.7, были выделены красным, а меньшие или равные $3.45 – зелёным.
Замечание: Снимки экрана для этого примера были сделаны в Excel 2010, однако, в Excel 2007 и 2013 кнопки, диалоговые окна и настройки будут точно такие же или с незначительными отличиями.
Итак, вот, что нужно сделать по шагам:
- Выделите таблицу или диапазон, в котором Вы хотите изменить цвет заливки ячеек. В этом примере мы выделяем $B$2:$H$10 (заголовки столбцов и первый столбец, содержащий названия штатов, не выделяем).
- Откройте вкладку Home (Главная), в разделе Styles (Стили) нажмите Conditional Formatting (Условное форматирование) >New Rule (Создать правило).
- В верхней части диалогового окна New Formatting Rule (Создание правила форматирования) в поле Select a Rule Type (Выберите тип правила) выберите Format only cells that contain (Форматировать только ячейки, которые содержат).
- В нижней части диалогового окна в поле Format Only Cells with (Форматировать только ячейки, для которых выполняется следующее условие) настройте условия для правила. Мы выбираем форматировать только ячейки с условием: Cell Value (Значение ячейки) – greater than (больше) – 3.7, как показано на рисунке ниже.Далее нажмите кнопку Format (Формат), чтобы выбрать, какой цвет заливки должен быть применён, если выполняется заданное условие.
- В появившемся диалоговом окне Format Cells (Формат ячеек) откройте вкладку Fill (Заливка) и выберите цвет (мы выбрали красноватый) и нажмите ОК.
- После этого Вы вернетесь в окно New Formatting Rule (Создание правила форматирования), где в поле Preview (Образец) будет показан образец Вашего форматирования. Если всё устраивает, нажмите ОК.
Результат Ваших настроек форматирования будет выглядеть приблизительно так:
Так как нам нужно настроить ещё одно условие, позволяющее изменять цвет заливки на зелёный для ячеек со значениями меньшими или равными 3.45, то снова нажимаем кнопку New Rule (Создать правило) и повторяем шаги с 3 по 6, устанавливая нужное правило. Ниже виден образец созданного нами второго правила условного форматирования:
Когда все будет готово – жмите ОК. Теперь у Вас имеется мило отформатированная таблица, которая даёт возможность с первого взгляда увидеть максимальные и минимальные цены на бензин в разных штатах. Хорошо им там, в Техасе!
Совет: Таким же способом Вы можете изменять цвет шрифта в зависимости от значения ячейки. Для этого просто откройте вкладку Font (Шрифт) в диалоговом окне Format Cells (Формат ячеек), как мы это делали на шаге 5 , и выберите желаемый цвет шрифта.
Как настроить постоянный цвет ячейки, основываясь на её текущем значении
Однажды настроенный цвет заливки не будет изменяться, вне зависимости от того, как в будущем изменится содержимое ячейки.
Задача: Вы хотите настроить цвет ячейки, основываясь на её текущем значении, и хотите, чтобы цвет заливки оставался неизменным, даже когда значение ячейки изменяется.
Решение: Найти все ячейки с определённым значением (или значениями) при помощи инструмента Find All (Найти все), а затем изменить формат найденных ячеек, используя диалоговое окно Format Cells (Формат ячеек).
Это одна из тех редких задач, по которым нет разъяснения в файлах справки Excel, на форумах или в блогах, и для которых нет прямого решения. И это понятно, так как эта задача не типичная. И всё же, если Вам нужно изменить цвет заливки ячеек окончательно, то есть раз и на всегда (либо пока Вы не измените его вручную), выполните следующие шаги.
Найти и выделить все ячейки, удовлетворяющие заданному условию
Тут возможно несколько сценариев, в зависимости от того, значения какого типа Вы ищите.
Если Вы хотите раскрасить ячейки с конкретным значением, например, 50, 100 или 3.4 – то на вкладке Home (Главная) в разделе Editing (Редактирование) нажмите Find Select (Найти и выделить) > Find (Найти).
Введите нужное значение и нажмите Find All (Найти все).
Совет: В правой части диалогового окна Find and Replace (Найти и заменить) есть кнопка Options (Параметры), нажав которую Вы получите доступ к ряду продвинутых настроек поиска, таких как Match Case (Учитывать регистр) и Match entire cell content (Ячейка целиком). Вы можете использовать символы подстановки, такие как звёздочка (*), чтобы найти любую строку символов, или знак вопроса (?), чтобы найти один любой символ.
Что касается предыдущего примера, если нам нужно найти все цены на бензин от 3.7 до 3.799, то мы зададим такие критерии поиска:
Теперь кликните любой из найденных элементов в нижней части диалогового окна Find and Replace (Найти и заменить) и нажмите Ctrl+A, чтобы выделить все найденные записи. После этого нажмите кнопку Close (Закрыть).
Вот так можно выделить все ячейки с заданным значением (значениями) при помощи опции Find All (Найти все) в Excel.
Однако, в действительности нам нужно найти все цены на бензин, превышающие $3.7. К сожалению, инструмент Find and Replace (Найти и заменить) в этом не сможет нам помочь.
Свойство .Interior.Color объекта Range
Начиная с Excel 2007 основным способом заливки диапазона или отдельной ячейки цветом (зарисовки, добавления, изменения фона) является использование свойства .Interior.Color объекта Range путем присваивания ему значения цвета в виде десятичного числа от 0 до 16777215 (всего 16777216 цветов).
Читайте также: