Excel заливка ячейке макрос

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

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

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

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

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

Sub ColorTest1()

Range(«A1»).Interior.Color = 31569

Range(«A4:D8»).Interior.Color = 4569325

Range(«C12:D17»).Cells(4).Interior.Color = 568569

Cells(3, 6).Interior.Color = 12659

End Sub

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

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

Sub ColorTest11()

Cells(1, 1).Interior.Color = —12207890

Cells(2, 1).Interior.Color = 16777215 + (—12207890)

Cells(3, 1).Interior.Color = 4569325

End Sub

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

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

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

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

Sub ColorTest2()

MsgBox Range(«A1»).Interior.Color

MsgBox Range(«A4:D8»).Interior.Color

MsgBox Range(«C12:D17»).Cells(4).Interior.Color

MsgBox Cells(3, 6).Interior.Color

End Sub

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

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

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

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

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

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

Range(«A1»).Interior.Color = vbGreen

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

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

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

Палитра Excel

Палитра Excel

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

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

Range(«A1»).Interior.Color = RGB(100, 150, 200)

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

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

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

Range(«A1»).Interior.Color = xlNone

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

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

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

Range(«A1»).Interior.ColorIndex = 8

MsgBox Range(«A1»).Interior.ColorIndex

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

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

Sub ColorIndex()

Dim i As Byte

For i = 1 To 56

Cells(i, 1).Interior.ColorIndex = i

Next

End Sub

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

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

Источник
 

Доброго времени суток, Друзья!
Подскажите как создать макрос по условию
К примеру если значение ячейки H = -7, I = -4, J = 5 и O = 0.85, то окрасить ячейку Q в желтый цвет, а так же
если H = -10, I = 22, J = 27 и O = 0.68, то окрасить ячейку R в желтый цвет
Всем заранее спасибо
 

Bema

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

Сообщений: 6750
Регистрация: 15.02.2016

Алексей, добрый и Вам. Обязательно нужен макрос? Можно и при помощи УФ такое сделать.

Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл

 
Bema

, и Вам доброго….без макроса никуда к сожалению
Очень много данных в таблице реальной…сюда ее не загрузить…весит почти 10Mb
 

Bema

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

Сообщений: 6750
Регистрация: 15.02.2016

Дело хозяйское ;)  

Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл

 

А если бы в макросе были бы еще комментарии что за что отвечает и счетчик сколько то или иное условие встречалось ранее(вывод счетчика в той же окрашенной ячейки), то этот макрос был бы очень хорошим)
Ну как минимум можно было бы в дальнейшем при создании подобного условия легко разобраться как его сделать
 

Kuzmich

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

Сообщений: 7998
Регистрация: 21.12.2012

Сделайте небольшой пример
 
Kuzmich

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

Kuzmich

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

Сообщений: 7998
Регистрация: 21.12.2012

#8

05.07.2017 21:36:15

Макрос (в модуль листа) срабатывает на изменение значений в ячейках столбцов H:O
Счетчик сколько то или иное условие встречалось внизу таблицы

Код 
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("H:O")) Is Nothing Then
        Application.EnableEvents = False
Dim i As Long
Dim iLastRow As Long
 iLastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("Q1:R" & iLastRow).ClearContents
    Range("Q1:R" & iLastRow).Interior.ColorIndex = xlColorIndexNone
  For i = 1 To iLastRow
    If Cells(i, 8) = "­7" And Cells(i, 9) = "­4" And Cells(i, 10) = "5" And Cells(i, 15) = "0.85" Then
      Cells(i, 17) = 1
      Cells(i, 17).Interior.ColorIndex = 6
    End If
    If Cells(i, 8) = "­10" And Cells(i, 9) = "22" And Cells(i, 10) = "27" And Cells(i, 15) = "0.68" Then
      Cells(i, 18) = 1
      Cells(i, 18).Interior.ColorIndex = 6
    End If
  Next
     Range("Q" & iLastRow + 1) = Application.Sum(Range("Q1:Q" & iLastRow))
     Range("R" & iLastRow + 1) = Application.Sum(Range("R1:R" & iLastRow))
End If
    Application.EnableEvents = True
End Sub
 
Kuzmich

, спасибо большое, но у меня чего-то не работает))))
Подозреваю что нужно подключать библиотеки…у меня макбук, а на обычной Виндовс сейчас пока не проверить
И еще маленький вопрос…

Kuzmich

, скажите, а этот счетчик можно вывести именно в ту ячейку которая должна окрасится?
Заранее спасибо

Изменено: Aleksey.g84 — 05.07.2017 21:54:57
 

Kuzmich

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

Сообщений: 7998
Регистрация: 21.12.2012

#10

05.07.2017 22:23:05

Цитата
но у меня чего-то не работает

Скопируйте и вставьте код при русской раскладке клавиатуры
Я в ваш файл вставил код и он заработал при изменении значений в ячейках столбцов H:O
 
Kuzmich

, не помогает смена раскладки….
 

Kuzmich

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

Сообщений: 7998
Регистрация: 21.12.2012
 
Kuzmich

, грешу все таки на то, что у меня макбук…
смотрите…удалил уже отмеченные значения…очистил заливку
добавил ниже строчки и ничего не произошло…залез в макросы, выбрал макрос Айсумма, нажал на выполнить и так же тишина)))
 

Kuzmich

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

Сообщений: 7998
Регистрация: 21.12.2012

#14

05.07.2017 23:11:29

Сравните ваши строки в макросе

Код 
  For i = 1 To iLastRow
    If Cells(i, 8) = "_7" And Cells(i, 9) = "_4" And Cells(i, 10) = "5" And Cells(i, 15) = "0.85" Then
      Cells(i, 17) = 1
      Cells(i, 17).Interior.ColorIndex = 6
    End If
    If Cells(i, 8) = "_10" And Cells(i, 9) = "22" And Cells(i, 10) = "27" And Cells(i, 15) = "0.68" Then
      Cells(i, 18) = 1
      Cells(i, 18).Interior.ColorIndex = 6
    End If
  Next

И то, что у меня в примере

Изменено: Kuzmich — 05.07.2017 23:27:50
 

Aleksey.g84

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

Сообщений: 80
Регистрация: 18.05.2017

#15

05.07.2017 23:14:30

совершенно идентичны…

Код 
For i = 1 To iLastRow
    If Cells(i, 8) = "_7" And Cells(i, 9) = "_4" And Cells(i, 10) = "5" And Cells(i, 15) = "0.85" Then
      Cells(i, 17) = 1
      Cells(i, 17).Interior.ColorIndex = 6
    End If
    If Cells(i, 8) = "_10" And Cells(i, 9) = "22" And Cells(i, 10) = "27" And Cells(i, 15) = "0.68" Then
      Cells(i, 18) = 1
      Cells(i, 18).Interior.ColorIndex = 6
    End If
 Next
 

Kuzmich

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

Сообщений: 7998
Регистрация: 21.12.2012

У вас в коде «_7», «_4» и «_10»
Скопируйте значения -7, -4 и -10 прямо из соответствующих ячеек в код макроса.
 

И все же

Kuzmich

продолжаю грешить на то, что у меня макбук….сделал как вы сказали…скопировал прямо из книги в редактор…сохранил и ничего)))
 

Kuzmich

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

Сообщений: 7998
Регистрация: 21.12.2012

Мой пример из сообщения 12 у вас работает?
 
Kuzmich

, к сожалению нет….
пробовал по разному…добавил строчек…ничего
закрыл эксель и не сохранил….
открыл еще раз….очистил уже установленные маркера…полез в макросы…нажал выполнить Айсумма…тоже ничего
 

vikttur

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

Сообщений: 47199
Регистрация: 15.09.2012

Для MAC есть отдельная ветка форума.
 

Kuzmich

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

Сообщений: 7998
Регистрация: 21.12.2012

#21

05.07.2017 23:37:30

Цитата
нажал выполнить Айсумма…тоже ничего

Не надо ничего нажимать. Sub iSumma() это от другого примера
Я вам писал
Макрос (в модуль листа) срабатывает на изменение значений в ячейках столбцов H:O
 
vikttur

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

Aleksey.g84

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

Сообщений: 80
Регистрация: 18.05.2017

#23

05.07.2017 23:42:56

Kuzmich

, немного поменял код…
сделал так

Код 
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("H")) Is Nothing Then
        Application.EnableEvents = False

получилось ничего)

Прикрепленные файлы

  • Снимок экрана 2017-07-05 в 23.42.37.png (99.31 КБ)
 

Kuzmich

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

Сообщений: 7998
Регистрация: 21.12.2012

Вы сделали то, что я вам говорил в #16 ?
 

Obelisk

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

Сообщений: 49
Регистрация: 21.06.2017

У меня так получилось, все работает:
 

vikttur

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

Сообщений: 47199
Регистрация: 15.09.2012

#26

05.07.2017 23:49:36

Цитата
Aleksey.g84 написал: на данный момент это похоже на расизм

Да при чем здесь рассизм?! MAC имеет отличия в работе с VBA
 
Kuzmich

, совершенно верно….сейчас еще раз повторил….
 
vikttur

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

Kuzmich

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

Сообщений: 7998
Регистрация: 21.12.2012

#29

05.07.2017 23:56:38

Obelisk

Ваш макрос только для первой строки
и надо

Код 
Sub Макрос1()
Range("Q1").Interior.Color = vbYellow
End Sub
 

Obelisk

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

Сообщений: 49
Регистрация: 21.06.2017

#30

05.07.2017 23:58:27

Цитата
Aleksey.g84 написал:
то окрасить ячейку Q в желтый цвет

я так понял..
Источник

В данном примере описаны макросы для автоматического форматирования или спроса формата для ячеек таблиц Excel средствами VBA.

VBA-макрос: заливка, шрифт, линии границ, ширина столбцов и высота строк

В процессе запыления данных сотрудниками отдела на некоторых листах были изменены форматы ячеек:

запыления планов работ.

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

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

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

Чтобы написать свой код макроса откройте специальный VBA-редактор в Excel: «РАЗРАБОТЧИК»-«Код»-«Visual Basic» или нажмите комбинацию клавиш ALT+F11:

Код Visual Basic.

В редакторе создайте новый модуль выбрав инструмент «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

VBA-код макроса.

Теперь если нам нужно сбросить форматирование таблицы на исходный формат отображения ее данных, выделите диапазон ячеек A1:E20 и запустите макрос: «РАЗРАБОЧТИК»-«Код»-«Макросы»-«SbrosFormat»-«Выполнить». Результат работы макроса изображен ниже на рисунке:

сбросить форматирование таблицы на исходный формат.

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



Описание VBA-макроса для формата ячеек таблицы Excel

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

  1. Текст в значениях ячеек выравнивается по центру горизонтально и вертикально.
  2. Включен построчный перенос текста.
  3. Все границы ячеек получают черную обычной толщины непрерывную линию с черным цветом.
  4. Сброс цвета шрифта на авто.
  5. Удаляется любая заливка ячеек.
  6. Ширина столбцов автоматически настраивается под текст в ячейках.
  7. Автоматически настроить высоту строк по содержимому ячеек.

Модификация исходного кода макроса для форматирования

Если необходимо сделать так чтобы текст выравнивался не по центру относительно горизонтали, а по правую сторону ячейки, тогда измените константу xlHAlignCenter на xlHAlignRight. Она находиться в свойстве .HorizontalAlignment. Сделайте это следующим образом:

.HorizontalAlignment = xlHAlignRight.

Таким же образом можно выровнять текст по левую сторону изменив значение константы на xlHAlignLeft. Или можно выровнять положение текста по ширине ячейки используя константу xlHAlignJustify.

Чтобы макрос выравнивал текст в ячейках по вертикали к низу, измените строку кода, отвечающую за данную настройку форматирования. Измените константу, которая присваивается к свойству VerticalAlignment в следующий способ:

Если хотите выровнять текс к верху ячейки, тогда воспользуйтесь константой xlHAlignTop.

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

.Borders.LineStyle = xlDash

.Borders.Color = vbBlue

.Borders.Weight = xlMedium

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

  • xlDoshDot – применяется для рисования пунктирных линий в границах ячеек;
  • xlDouble – рисует двойную линию;
  • xlHairLine – рисует тонкую линию;
  • xlThick – для рисования очень толстой линии.

Для настройки цвета линий Excel предлагает всего 8 констант для определенных цветов. Константы для настройки цвета линий границ для свойства Color:

  • vbBlack – черный;
  • vbWhite – белый;
  • vbRed – красный;
  • vbGreen –зеленый;
  • vbBlue – синий;
  • vbYellow – желтый;
  • vbMagenta – алый;
  • vbCyan – голубой.

Но при необходимости присвоить линиям границ другие цвета можно вместо константы для свойства Color записать функцию RGB(). Достаточно лишь в аргументе этой функции указать код цвета по шкале от 0 и до 255.

Если нужно применить толстую линию только для границ выделенного диапазона, тогда перед инструкцией End With добавьте следующую строку кода:

.BorderAround xlContinuous, xlMedium, vbBlack

Описание: В первом аргументе для метода BorderAround можно записать также другой стиль линии. Во втором – толщину линии, а в третьем – цвет. Константы, которые можно присвоить в качестве значений для этих аргументов можно использовать те же, которые мы использовали для свойств: LineStyle, Weight, Color.

Если нужно экспонировать первую строку для выделенного диапазона с помощью жирного и курсивного шрифта значений ячеек. А также заполнить ячейки первой строки заливкой с голубым цветом, тогда в самом конце кода макроса перед последней инструкцией End Sub следует добавить несколько строк с VBA-кодом:

.Rows(1).Font.Bold = True

.Rows(1).Font.Italic = True

.Rows(1).Interior.Color = vbCyan

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

.Columns (1).Font.Bold = True

.Columns (1).Font.Italic = True

.Columns (1).Interior.Color = vbCyan

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

.Rows(.Rows.Count).Font.Bold = True

Полная версия модифицированного кода макроса выглядит так:

Sub SbrosFormat()
If TypeName(Selection) <> "Range" Then Exit Sub
With Selection
.HorizontalAlignment = xlVAlignCenter
.VerticalAlignment = xlVAlignCenter
.WrapText = True
.Borders.LineStyle = xlDash
.Borders.Color = vbBlue
.Borders.Weight = xlMedium
.Font.ColorIndex = xlColorIndexAutomatic
.Interior.ColorIndex = xlColorIndexAutomatic
.Columns.AutoFit
.Rows.AutoFit
.BorderAround xlContinuous, xlMedium, vbBlack
.Rows(1).Font.Bold = True
.Rows(1).Font.Italic = True
.Rows(1).Interior.Color = vbCyan
.Columns(1).Font.Bold = True
.Columns(1).Font.Italic = True
.Columns(1).Interior.Color = vbCyan
.Rows(.Rows.Count).Font.Bold = True
End With
End Sub

Пример работы измененного кода VBA-макроса:

Пример после изменений в коде.

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

Источник

Содержание

  1. VBA Excel. Цвет ячейки
  2. Свойство .Interior.Color объекта Range
  3. Заливка ячейки цветом в VBA Excel
  4. Вывод сообщений о числовых значениях цветов
  5. Использование предопределенных констант
  6. Цветовая модель RGB
  7. Свойство .Interior.ColorIndex объекта Range
  8. VBA Excel. Цвет ячейки (заливка, фон)
  9. Свойство .Interior.Color объекта Range
  10. Заливка ячейки цветом в VBA Excel
  11. Вывод сообщений о числовых значениях цветов
  12. Использование предопределенных констант
  13. Цветовая модель RGB
  14. Очистка ячейки (диапазона) от заливки
  15. Свойство .Interior.ColorIndex объекта Range
  16. 86 комментариев для “VBA Excel. Цвет ячейки (заливка, фон)”
  17. Использование макроса для применения формата заливки ячеек ко всем другим строкам в выбранном диапазоне в Excel
  18. Аннотация
  19. Дополнительная информация
  20. Пример процедуры Visual Basic

VBA Excel. Цвет ячейки

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

  1. Свойство .Interior.Color объекта Range
    • Заливка ячейки цветом в VBA Excel
    • Вывод сообщений о числовых значениях цветов
    • Использование предопределенных констант
    • Цветовая модель RGB
  2. Свойство .Interior.ColorIndex объекта Range

Свойство .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 Желтый

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

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

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

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

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

Открывается в новом окне Палитра Excel

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

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

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

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

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

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

Источник

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:

Чтобы можно было присвоить ячейке или диапазону цвет с помощью значений 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 цветов, а также о том, как добавить узор в ячейку.

86 комментариев для “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 фигурирует (С+) с целью акцентировать на этом внимание.
Заранее спасибо за ответ.

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

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

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

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

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

Источник

Использование макроса для применения формата заливки ячеек ко всем другим строкам в выбранном диапазоне в Excel

Аннотация

Microsoft Excel автоматически форматирует новые данные, которые вы введите в конце списка, в соответствии с предыдущими строками. Вы также можете отформатировать список программным способом. В этой статье приведен пример процедуры Microsoft Visual Basic для приложений для затенение каждой другой строки в выделенном фрагменте.

Дополнительная информация

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

Чтобы просмотреть шаблоны, доступные в Microsoft Office Excel 2003 и более ранних версиях, щелкните «Ячейки» в меню «Формат» и откройте вкладку «Шаблоны«.

Чтобы просмотреть шаблоны, доступные в Microsoft Office Excel 2007, выполните следующие действия.

  1. На вкладке «Главная » щелкните «Формат » в группе «Ячейки «. Затем нажмите кнопку «Формат ячеек».
  2. В диалоговом окне «Формат ячеек» откройте вкладку «Заливка «.

В раскрывающемся списке «Шаблон» отображаются доступные шаблоны. Шаблон, используемый в следующем макросе, который называется постоянным именем xlGray16, является пятым справа в первой строке.

Следующий макрос задает для каждой другой строки текущего выделения значение xlGray16.

Пример процедуры Visual Basic

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

Этот процесс также можно выполнить вручную с помощью условного форматирования.

Дополнительные сведения о форматировании каждой другой строки с помощью условного форматирования см. в разделе «Применение заливки к альтернативным строкам на листе».

Вы также можете отформатировать список с помощью команды меню «Автоформат «. В Excel 2003 и Microsoft Excel 2002 команда меню «Автоформат» находится в меню «Формат«. В Excel 2007 необходимо добавить команду меню «Автоформат» на панель быстрого доступа. Для этого выполните следующие действия:

  1. Нажмите кнопку Microsoft Office, а затем нажмите кнопку «Параметры Excel».
  2. Нажмите кнопку «Настройка конструктора».
  3. Щелкните, чтобы выбрать все команды в разделе «Выбрать команды».
  4. Нажмите кнопку «Автоформат«, нажмите кнопку «Добавить» и нажмите кнопку » ОК».

Источник

Источник

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

  1. Свойство .Interior.Color объекта Range
    • Заливка ячейки цветом в VBA Excel
    • Вывод сообщений о числовых значениях цветов
    • Использование предопределенных констант
    • Цветовая модель RGB
  2. Свойство .Interior.ColorIndex объекта Range

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

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

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

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

1

2

3

4

5

6

Sub ColorTest1()

Range("A1").Interior.Color = 31569

Range("A4:D8").Interior.Color = 4569325

Range("C12:D17").Cells(4).Interior.Color = 568569

Cells(3, 6).Interior.Color = 12659

End Sub

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

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

1

2

3

4

5

Sub ColorTest11()

Cells(1, 1).Interior.Color = -12207890

Cells(2, 1).Interior.Color = 16777215 + (-12207890)

Cells(3, 1).Interior.Color = 4569325

End Sub

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

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

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

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

1

2

3

4

5

6

Sub ColorTest2()

MsgBox Range("A1").Interior.Color

MsgBox Range("A4:D8").Interior.Color

MsgBox Range("C12:D17").Cells(4).Interior.Color

MsgBox Cells(3, 6).Interior.Color

End Sub

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

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

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

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

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

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

1

Range("A1").Interior.Color = vbGreen

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

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

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

Палитра ExcelОткрывается в новом окне

Палитра Excel

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

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

1

Range("A1").Interior.Color = RGB(100, 150, 200)

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

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

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

1

2

Range("A1").Interior.ColorIndex = 8

MsgBox Range("A1").Interior.ColorIndex

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

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

1

2

3

4

5

6

Sub ColorIndex()

Dim i As Byte

For i = 1 To 56

Cells(i, 1).Interior.ColorIndex = i

Next

End Sub

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

Готовую стандартную палитру из 56 цветов можете посмотреть здесь .

Источник

Понравилась статья? Поделить с друзьями:

А вот еще интересные статьи:

  • Excel заливка ячеек по образцу
  • Excel заливка ячеек автоматически
  • Excel заливка через одну строчку
  • Excel заливка строк через одну
  • Excel задать переход по ячейкам

    • 0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии