Remkomplekty.ru

IT Новости из мира ПК
9 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды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 есть предопределенные константы часто используемых цветов для заливки ячеек:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

как проверить наличие фона?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читать еще:  Активная ячейка в excel это

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

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 ? (или я сморозил…).
Заранее спасибо за ответ.

VBA: извлечение значения RGB строк в диаграмме с цветами по умолчанию

7 Alice [2014-09-13 19:32:00]

Проблема

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

USECASE

Мое фактическое использование — это то, что я хотел бы, чтобы datalabels соответствовали цвету линий/маркеров в линейной диаграмме. Это легко, если я явно задал цвета серии через схему или явные значения RGB, например.

Однако, делая это при присвоении цвета серии, автоматически выводятся белые метки. Более конкретно, обе следующие равенства имеют значение

И все же линия, конечно, не белая, но автоматически назначается цвет из темы. Это показывает, что цвет автоматически назначается

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

Уродливое обходное решение

Для диаграмм с 6 или менее записей простой способ заключается в том, чтобы использовать тот факт, что цвета тем назначаются последовательно, поэтому я могу сделать (например)

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

Исследование

Кто-то задал практически тот же вопрос (как извлечь цвета тем) здесь, но на него никогда не отвечали. Существует несколько источников, предлагающих способы преобразования известного цвета темы в значения RGB (например, здесь и здесь), но это просто вызывает вопрос; Я не знаю цвета априори, кроме «любого цвета, на который в данный момент находится эта строка».

vba excel-vba colors

3 ответа

7 Решение David Zemens [2014-09-13 21:40:00]

Так что это интересно. Я создаю линейную диаграмму, используя все значения по умолчанию, а затем запускаю эту процедуру:

В окне Immediate отображается следующее:

Но это явно не так. Очевидно, что все они разные. Если вместо .RGB я делаю .ObjectThemeColor , тогда я получаю все 0 , что одинаково и явно ложно, наблюдая за диаграммой!

Теперь здесь интересно:

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

Как будто Excel (и PowerPoint/etc.) полностью не могут распознать автоматически назначенные цвета в линейных диаграммах. Как только вы назначаете цвет, он может читать цвет.

ПРИМЕЧАНИЕ. Линейные диаграммы являются разборчивыми, потому что у вас нет .Fill , а скорее .Format.Line.ForeColor (и .BackColor ) и IIRC есть и другие причуды, например, вы можете выбрать отдельную точку и изменить ее цвет, а затем это повлияет на внешний вид предыдущего сегмента линии и т.д.

Ограничено ли это линейными диаграммами? Возможно. Мой прошлый опыт говорит «возможно», хотя я не в состоянии сказать, что это ошибка, это, безусловно, кажется ошибкой.

Если я запустил аналогичную процедуру в таблице столбцов — снова используя только автоматически назначенные цвета по умолчанию,

Затем я получаю то, что представляется допустимым значением RGB:

ОДНАКО: Он по-прежнему не распознает действительный ObjectThemeColor . Если я изменю .RGB , тогда это выведет:

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

Как утверждает Тим ​​Уильямс, это была ошибка еще в 2005 году по крайней мере, поскольку она относится к RGB, и, вероятно, эта ошибка перенесена в Excel 2007+ с ObjectThemeColor и т.д. Это вряд ли быть разрешенным в ближайшее время, поэтому нам нужно решение для взлома:)

Читать еще:  Как сохранить текст в excel

ОБНОВЛЕННОЕ РЕШЕНИЕ

Объедините два метода выше! Преобразуйте каждую строку из строки в xlColumnClustered , затем запросите свойство цвета из .Fill , а затем измените тип диаграммы рядов на исходное состояние. Это может быть более надежным, чем попытка использовать последовательную индексацию (которая не будет надежной вообще, если пользователи повторно заказали серию, например, так, что «Series1» имеет индекс 3 и т.д.).

Как получить цвета по цветовой шкале условного форматирования Excel 2012 через код VBA

Мне нужно знать: Как получить цвета, сделанные цветовой гаммой условного форматирования Excel 2010 через код VBA. Эти цвета будут впоследствии назначены VBA в качестве фона диаграммы в соответствии со следующим изображением:

Я провел исследование на различных веб-сторонах и:

  1. большинство людей советуют, как читать цвет условного форматирования по методике .FormatConditions(index that is active).Interior.ColorIndex но в моем случае это не работает из-за ошибки «объект не поддерживает это свойство или метод»
  2. некоторые люди советуют написать собственное вычисление цветов (на основе значения ячеек). Я нашел различные способы, как это сделать, но ни один из них не может вычислить те же цвета, которые были вычислены ранее excel (те же цвета, что и на предыдущем рисунке).

поэтому я спрашиваю:

  1. есть ли способ непосредственно готовые цвета из ячеек? (или эти цвета недоступны для API)
  2. вы знаете, как вычислить те же цвета, что и Excel compute?
  3. вы знаете другой способ, как решить мою проблему?

Я считаю, что он должен как-то работать.

6 ответов

если нет лучшего ответа, вы можете попробовать это временное решение:

  1. ссылка / копирование данных в ячейки под диаграммой (с формулами, такими как =Sheet1!A1 )
  2. применить то же условное форматирование
  3. скрыть значения (с пользовательским форматом номера, как «» , т. е. пустой строковый литерал (2 двойные кавычки))
  4. сделать график прозрачным
  5. выровнять ячейки с график

обновление:

или вы можете попытаться вычислить цвет линейным приближением для каждого канала R, G, B, если условный формат использует только 2 базовых цвета (r1, g1, b1) и (r2, g2, b2) для 2 угловых случаев, которые могут быть

  • min и max стоимостью, например: 0 — 4 000
  • min и max процентов, например: 10% — 90%
    (я считаю, что вы можете использовать % * [массив — min_value], чтобы получить фактическое значение)
  • min и max процентиль, например: 0-й процентиль-100-й процентиль

для параметров процентов / процентилей сначала необходимо преобразовать фактическое значение в значение процентов / процентилей, а затем, если value или value > max используйте угловые цвета, в противном случае:

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

EDIT: я только что протестировал это с довольно маленькой матрицей 4×8 ячеек/диаграмм, и производительность довольно плохая! Может быть, лучше просто вставить без ссылки:=True .

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

Categorized | excel tips

Excel Color Palette and Color Index change using VBA

Excel Color Palette has an index of 56 colors which can be modified using VBA. Each color in the palette is associated with a unique value in the index that can be changed programatically. At times it is useful to know the relative positioning of the various colors within this index as well as how various versions of Excel treat colors. This is the first in the series of articles that will expore this further.

How to generate Excel Color Index using VBA

Here’s is a sample code snippet (with a few minor modifications) from mvps.org that helps one generate a color palette and place it an Excel worksheet :

Sub colors56()
’57 colors, 0 to 56

Dim i As Long
Dim str0 As String , str As String
Cells(1, 1) = «Interior»
Cells(1, 2) = «Font»
Cells(1, 3) = «HTML»
Cells(1, 4) = «RED»
Cells(1, 5) = «GREEN»
Cells(1, 6) = «BLUE»
Cells(1, 7) = «COLOR»

For i = 0 To 56
Cells(i + 2, 1).Interior.ColorIndex = i
Cells(i + 2, 2).Font.ColorIndex = i
Cells(i + 2, 2).Value = «[Color » & i & «]»
str0 = Right( «000000» & Hex(Cells(i + 2, 1).Interior.Color), 6)
‘Excel shows nibbles in reverse order so make it as RGB
str = Right(str0, 2) & Mid(str0, 3, 2) & Left(str0, 2)
‘generating 2 columns in the HTML table
Cells(i + 2, 3) = «#» & str
Cells(i + 2, 4).Formula = «=Hex2dec(» «» & Right(str0, 2) & «» «)»
Cells(i + 2, 5).Formula = «=Hex2dec(» «» & Mid(str0, 3, 2) & «» «)»
Cells(i + 2, 6).Formula = «=Hex2dec(» «» & Left(str0, 2) & «» «)»
Cells(i + 2, 7) = «[Color » & i & «]»
Next i
End Sub

The output of the code will be something akin to what is shown below.

(Please note: Values -1 and 0 can be assigned to an object. However you cannot change those color values in the palette which would lead me to assume that Excel provides 58 (56 + 2) assignable color values while there being only 56 modifiable color values.)

Look’s downright ugly isn’t it. The interesting thing is that there seems to be no apparent logic to the allocation of color index values to various colors – it neither proceeds from light (#FFFFFF) to dark (#000000) or follow a sequential numbering pattern in the palette (left to right or up to down). One would think that the simplest way for anyone to create the palette would have been to number the colors in some logical fashion so that those colors could be modified using a program far more easily than by having to remember the position of each individual color in the palette.

Читать еще:  Rowsource vba excel

You can download a sample worksheet with the Excel Color Index and Palette with VBA code here or click on the button below:

How to change Excel Color Palette using VBA

You can use the assign a new color to a the palette at a particular index position by simple specifying the index number and then using the .Colors function to assign a new RGB value to it. The R, G and B signify the red, green and blue hues that make up the color. (You can get the color index from the index chart shown above)

VBA RGB

Excel VBA RGB Color

RGB can be also termed as red green and blue, this function is used to get the numerical value of the color value, this function has three components as a named range and they are red, blue and green the other colors are considered as the components of these three different colors in VBA.

In VBA everything boils down to coding of each and every piece, for an example, if you want to reference some portion of the worksheet then we can use RANGE object, if you want to change the font colour then we can make use of NAME property of the range then write font name that we needed but imagine a situation of changing the colour of the font or background colour of the cell for this we can make use of built-in VB colors like, vbGreen, vbBlue, vbRed, etc…But we have dedicated function to play around with different colours i.e. RGB function.

Below is the syntax of the RGB color function.

As you can see above we can supply three arguments i.e. Red, Green, and Blue. All these three parameters can accept integer numbers ranging from 0 to 255 only and the result of this function will be “Long” data type.

Change Color of Cells using VBA RGB Function

Example #1

For example, we have numbers from cell A1 to A8 as shown in the below image.

For this range of cells, we will try to change the font color to some random color by using RGB function.

Start the macro procedure first.

Code:

First, we need to reference the range of cells of fonts we want to change the color of, in this case, our range of cells is A1 to A8, so supply the same by using RANGE object.

Code:

Put dot to see the IntelliSense list of RANGE object, from the IntelliSense list we are trying to changing the color of the font, and so choose FONT property from the list.

Code:

Once the FONT property was chosen in this property we are trying to change the color, so choose the color property of the FONT.

Code:

Put equal sign and open RGB function.

Code:

Give random integer numbers ranging from 0 to 255 for all the three arguments of the RGB function.

Code:

Ok, now run the code and see the result of font colors of the cells from A1 to A8.

Output:

So, the colors of the font changed from black to some other. Color depends on the numbers we give to the RGB function.

Below are RGB color codes to get some of the common colors.

You can just change the integer number combination from 0 to 255 to get the different sorts of colors.

Example #2

For the same range of cells let’s see how to change the background colour of these cells.

First, supply the range of cells by using the RANGE object.

Code:

This time we are changing the background color of the mentioned cells, so we have nothing to do with FONT property now to change background color choose “Interior” property of the RANGE object.

Code:

Once the “Interior” property is selected put dot to see the properties and methods of this “Interior” property.

Code:

Since we are changing the interior color of the mentioned cells choose “Color” property.

Code:

To set the interior color property of the range of cells (A1 to A8) out the equal sign and open RGB function.

Code:

Enter the random number as you want.

Code:

Run the code and see the background color.

Output:

The background colour has been changed.

Things to Remember Here

  • RGB stands for Red, Green, and Blue.
  • A combination of these three colors will give different colors.
  • All these three parameters can accept integer values between 0 to 255 only. Any numbers above this will be reset to 255.

Recommended Articles

This has been a guide to VBA RGB. Here we discuss how to change the color of the interior cell (background, font) in excel VBA by putting different integer numbers in RGB function with examples and downloadable excel template . Below are some useful excel articles related to VBA-

Ссылка на основную публикацию
Adblock
detector