Formatting cells fill with vba

Выбор цветов

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

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

На вкладке System (Системные) (рис. 19.1, слева) можно выбрать цвет из системной палитры Windows, т.е. из цветов, заданных в панели управления Windows для элементов интерфейса Windows. Если из списка выбрать пункт типа Desktop (Рабочий стол) или Button Face (Поверхность кнопки), VBA будет автоматически корректировать хранящееся в свойстве значение цвета при изменении соответствующих параметров в панели управления Windows.

Рис. 19.1. Здесь показаны обе вкладки диалогового окна выбора цвета в VBA

На вкладке Palette (Палитра) (рис. 19.1, справа) можно выбрать цвет из предлагаемой палитры. VBA автоматически назначит свойству числовое значение выбранного цвета. В общем, довольно просто.

Начиная с 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	Желтый

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

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

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

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

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

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

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

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

Заливка ячейки текущим цветом

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

Заливка строки цветомЗдравствуйте. Подскажите пожалуйста как мне изменить код. если в определенной ячейке макрос.

VBA Excel: Заливка цветомЗадача следующая: В ячейке могут появляться значения от 1 до 300. Нужно, что бы в зависимости от.

Рисунок, процентная заливка определённым цветом

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

Есть идея. Все получалось.

Идея парсинга и ручного «перевода» TooltipText в некий ColorID –это первое, что пришло мне в голову. Но я не стал это советовать, так как рассчитывал, что будет предложен вариант, основанный на «вытаскивании» ColorID непосредственно из самого CommandBar. Мне он неизвестен.

Следует использовать эти ссылки на контроли: CommandBars(«Drawing»).Controls(«Fill Color») У вас некое число 18, Controls(18), которое вообще говоря не будет работать.

У вас два массива и цикл. Их лучше заменить на Scripting.Dictionary и прямой безцикловый доступ.

В остальном у вас хороший пример. Неясно лишь, зачем применять Select. Вы первый, кто вообще понял суть вопроса. Другие ответы просто не в масть.

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

Весь ваш остальной код – бессмысленное нагромождение символов. Как вам это удалось?

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

Весь ваш остальной код – бессмысленное нагромождение символов. Как вам это удалось?

2 Evrodiller А это и не нужно — нужно выделить диапазон и одной этой строчки достаточно для его заливки.

Я так понял, что простого решения этого вопроса нет? Теперь у меня следующий вопрос: При выполнении этой строчки

2 Evrodiller Я прочел вопрос очень внимательно. Прочтите ж и вы и вопрос и мои комментарии. Вот эта линия:

Делает заливку всего диапазона, как верно вам на это указал и booqueworme. Беда лишь в том, что правая часть в операторе — простая как валенок константа. А требуется-то значение из палитры, которое может быть каким-угодно. И его надо знать, какое оно в данный момент. Вот это и есть суть вопроса.

Я и об Execute думал. У меня он выдает ошибку автоматизации, так что я его и не упоминал. Я не вижу красивого решения к первому вопросу. Видимо Микрософт перемудрил и сделал самое главное свойство палитры Private. Хотя направление в сторону Execute наверно и есть самое правильное.

На форуме VB6 есть два очень сильных участника: Comanche и Messir. Попробуйте просить их.

Синтаксис Syntax

RGB(красный, зеленый, синий) RGB(red, green, blue)

Синтаксис функции RGB состоит из следующих именованных аргументов: The RGB function syntax has these named arguments:

Часть Part	Описание Description
отображаем red	Обязательный аргумент; Variant (Integer). Required; Variant (Integer). Число в диапазоне 0–255 включительно, представляющее красный компонент цвета. Number in the range 0–255, inclusive, that represents the red component of the color.
Интенсив green	Обязательный аргумент; Variant (Integer). Required; Variant (Integer). Число в диапазоне 0–255 включительно, представляющее зеленый компонент цвета. Number in the range 0–255, inclusive, that represents the green component of the color.
компонентов blue	Обязательный аргумент; Variant (Integer). Required; Variant (Integer). Число в диапазоне 0–255 включительно, представляющее синий компонент цвета. Number in the range 0–255, inclusive, that represents the blue component of the color.

format.set_border()

set_border(style)

Set the cell border style.

Parameters

style () – Border style index. Default is 1.

Individual border elements can be configured using the following methods with
the same parameters:

A cell border is comprised of a border on the bottom, top, left and right.
These can be set to the same value using or individually
using the relevant method calls shown above.

The following shows the border styles sorted by XlsxWriter index number:

Index	Name	Weight	Style
None
1	Continuous	1
2	Continuous	2
3	Dash	1
4	Dot	1
5	Continuous	3
6	Double	3
7	Continuous
8	Dash	2
9	Dash Dot	1
10	Dash Dot	2
11	Dash Dot Dot	1
12	Dash Dot Dot	2
13	SlantDash Dot	2

The following shows the borders in the order shown in the Excel Dialog:

VBA Code to Change Cell Color Based on the Value of Another Cell

We can use VBA code to change the background color of cells in an Excel dataset based on the values of other cells.

The following dataset shows particular states’ annual sales and ratings.

We want to use VBA to change the background color of the cells in columns A and B based on the values of corresponding cells in column C.

We use the following steps:

1. Press Alt + F11 to open the Visual Basic Editor (VBE).
2. Click “Insert” on the menu bar and choose “Module” to insert a standard VBA module.

3. Copy and paste the following code into the module:

Note: You can customize the code for your needs by changing the target ranges and the value conditions.

4. Save the Workbook as an Excel Macro-Enabled Workbook, as shown in Example #1.
5. Press Alt + F11 to switch to the current workbook containing the dataset.
6. Press Alt + F8 to activate the “Macro” dialog box, select the “Change_Cell_Color” macro on the “Macro name” list box, and click “Run.”

The code executes and changes the background color of the cells in columns A and B, as shown below:

Explanation of the Code

The VBA code applies different background colors to cells in columns A and B based on the value in the corresponding cells in column C.

Red is used for values less than or equal to 200,000, yellow for values between 200,000 and 400,000, and green for values greater than or equal to 400,000.

Note: For this instance, we utilized color constants such as vbRed to modify the background color of cells instead of applying the RGB function, as demonstrated in Example #1.

How to permanently change a cell’s color based on its current value

Once set, the background color will not change no matter how the cell’s contents might change in the future.

Task: You want to color a cell based on its current value and wish the background color to remain the same even when the cell value’s changes.

Solution: Find all cells with a certain value or values using Excel’s Find All function or Select Special Cells add-in, and then change the format of found cells using the Format Cells feature.

This is one of those rare tasks that are not covered in Excel help files, forums and blogs and for which there is no straightforward solution. And this is understandable, because this task is not typical. And still, if you need to change the background color of cells statically i.e. once and forever unless you change it manually again, proceed with the following steps.

Font formatting – How to format fonts in VBA

The easiest way to format fonts, in example by setting its size, is to select the cell, then refer to the font and its specific property, in this case its size. Example is listed below.

If you would like to format more than one font property at the same time, you don’t need to repeat block of code. In this case, you can use the With clause . As in the first example, you will refer to the selected range. Then, change the font size, make it bold, and set the font to Arial.

Font formatting – How to format fonts in VBA

In this way, you can format any font property in Excel sheet. Below are listed the most important properties that you can use.

In VBA, colors can be set in several different ways. You can use both the official VBA naming preceded by the prefix name vb or the range of the RGB colors.

The name of VBA	The name of RGB
vbBlack	RGB (0, 0, 0)
vbWhite	RGB (255, 255, 255)
vbRed	RGB (255, 0, 0)
vbGreen	RGB (0, 255, 0)
vbBlue	RGB (0, 0, 255)
vbYellow	RGB (255, 255, 0)
vbMagenta	RGB (255, 0, 255)
vbCyan	RGB (0, 255, 255)

Example 1: Set the color of a cell / range

The .Interior.Color property is used to set the color of a cell or a range. There are various methods in which we can do this.

'Using XlRgbColor Enumeration - for few cells in a row
Range("B2:D2").Interior.Color = rgbDarkGreen

'Using Color Constants - for a cell using row and column number
Cells(3, 2).Interior.Color = vbYellow

'Specifying the RGB values - using A1 notation
Range("B4").Interior.Color = RGB(255, 0, 0)

'Using Color Code - for few cells in a column
Range("B5:B6").Interior.Color = 15773696

'Using Color Index - for a range
Range("B7:D8").Interior.ColorIndex = 7

This is how the output will look

For more details, refer to article Excel VBA, Cell Fill Color

Закрашивание ячеек в VBA Excel с использованием циклов

Для начала, необходимо указать диапазон ячеек, которые нужно закрасить. В VBA Excel можно использовать объект Range, чтобы определить диапазон ячеек. Например, следующий код определяет диапазон ячеек от A1 до A10:

Далее, можно использовать цикл For Each для прохождения по каждой ячейке в указанном диапазоне. В каждой итерации цикла можно установить цвет фона ячейки с помощью свойства Interior.Color:

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

Если нужно задать различные цвета для разных ячеек в диапазоне, можно использовать условные операторы, такие как If или Select Case. Например, следующий код закрашивает ячейки с нечетными номерами желтым цветом, а с четными — зеленым:

В этом примере используется переменная i, которая увеличивается на 1 на каждой итерации цикла. Оператор Mod используется для определения четности или нечетности значения переменной i.

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

Clear Colors in Excel VBA

Some times we need to fill no colors in Excel, we can clear the Excel Object colors such as font, border and fill colors and set to automatic or no fill color. Here are example macro to clear the color and fill no colors.

Clear Background Color in Excel VBA

We often required to clear the background or fill color of the excel object. We can use the following Excel Macro to clear the background colors and set no interior colors.

Sub SetClearBackgroundColor_ColorIndex_Range()
    Range("A1:E20").Interior.ColorIndex = -4142
End Sub

The above macro will set the Interior.ColorIndex to -4142 enumeration. Interior.ColorIndex = -4142 is enumeration to clear the background or fill color.

Similarly, we can clear the boder colors using Excel VBA as shown below:

Sub SetClearBorders_ColorIndex_Range()
    Range("A1:E20").Borders.ColorIndex = -4142
End Sub

We can set the font colors to default or automatic colors using Excel VBA ColorIndex property of Font object. Here is an example:

Sub SetClearFontColorIndex_Range()
    Range("A1:E20").Font.ColorIndex = -4105
End Sub

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

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

Textual

Sub Procedure_1()
«Создаём в оперативной памяти компьютера
«поименованную область — массив «myColor».
«Массив состоит из трёх строк и двух столбцов.
«Если надо искать более трёх слов, то нужно изменить
«здесь количество строк: сделать не 1 To 3, а например 1 to 4.
«Число столбцов изменять не надо.
Dim myColor(1 To 3, 1 To 2) As String
Dim rngSearch As Excel.Range
Dim rngFind As Excel.Range, myAddress As String
Dim i As Long
«1. Помещаем в массив «myColor» в первый столбец текст,
«который будет искаться в ячейках Excel.
«Во второй столбец помещаем номер цвета, который нужно
«будет применить.
«Номер цвета можно узнать так:
«1) переходите в Excel;
«2) делаете активной ячейку, у которой нужно узнать цвет;
«3) переходите в VBA;
«4) View — Immediate Window. Откроется окно. В это окно вставьте
«этот код и нажмите клавишу «Enter». Будет получен номер цвета.
«print activecell.Interior.Color
«5) Есть ещё «ColorIndex». Определённым цветам даны
«определённые номера. В справке в VBA (Excel 2010) если
«в верхнем правом углу вставить текст «ColorIndex»
«и нажать клавишу «Enter», то появится список статей.
«Выберите статью «ColorIndex Property». В ней показано,
«какому цвету, какой номер назначен.
«Числа заключаю в кавычки, т.к. я дал массиву тип данных «Текст».
myColor(1, 1) = «GR»: myColor(1, 2) = «5287936»
myColor(2, 1) = «RD»: myColor(2, 2) = «255»
myColor(3, 1) = «Y»: myColor(3, 2) = «65535»
«2. Для удобства написания кода, даём имя «rngSearch» диапазону ячеек, где
«нужно закрашивать ячейки. Через это имя будем обращаться
«к нужному диапазону ячеек.
Set rngSearch = ActiveSheet.Range(«A1:D25»)
«С помощью цикла с «i» просматриваем все строки в массиве «myColor».
«UBound(myColor, 1) — это порядковый номер последней строки в массиве «myColor».
For i = 1 To UBound(myColor, 1) Step 1
«3. Быстрее использовать не просмотр каждой ячейки, а быстрее вести поиск.
«LookAt:=xlWhole — ищется полное совпадение.
Set rngFind = rngSearch.Find(What:=myColor(i, 1), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
«Если найдено, то ячейке, в которой находится найденный текст,
«даётся имя «rngFind». И через это имя можно обращаться к ячейке.
«Если не найдено, то в переменной «rngFind» будет текст «Nothing».
If rngFind Is Nothing Then
«4. Если не найдено, то переходим к поиску следующего текста.
GoTo metka
End If
«Если найдено.
«5. Запоминаем адрес ячейки, где было первый раз найдено,
«чтобы потом остановить поиск.
myAddress = rngFind.Address
«С помощью цикла «Do … Loop» продолжаем поиск.
Do
«6. Окрашиваем ячейку нужным цветом.
«Число у нас содержится в виде текста. С помощью «CLng»
«делаем число числом.
rngFind.Interior.Color = CLng(myColor(i, 2))
«7. Продолжаем поиск.
Set rngFind = rngSearch.FindNext(rngFind)
Loop While rngFind.Address myAddress
metka:
Next i
«8. Сообщение, что работа кода завершена.
MsgBox «Работа кода завершена!», vbInformation
End Sub

Добавление дополнительного символа

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

Выделяем ячейку, в которой нужно установить прочерк, и ставим в ней с клавиатуры символ «‘». Он располагается на той же кнопке, что и буква «Э» в кириллической раскладке. Затем тут же без пробела устанавливаем символ «-».
Жмем на кнопку Enter или выделяем курсором с помощью мыши любую другую ячейку

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

Существует целый ряд способов установить в ячейку прочерк, выбор между которыми пользователь может сделать согласно целям использования конкретного документа. Большинство людей при первой неудачной попытке поставить нужный символ пытаются сменить формат ячеек. К сожалению, это далеко не всегда срабатывает. К счастью, существуют и другие варианты выполнения данной задачи: переход на другую строку с помощью кнопки Enter, использование символов через кнопку на ленте, применение дополнительного знака «’». Каждый из этих способов имеет свои достоинства и недостатки, которые были описаны выше. Универсального варианта, который бы максимально подходил для установки прочерка в Экселе во всех возможных ситуациях, не существует.

Solution 2: Adjusting The TintAndShade Property

I got the inspiration for this solution from a blog post over at Daily Dose of Excel. I love this solution because it is COMPLETELY reversible! However, for certain colors, it doesn’t work so great. For example try to lighten RGB(0,176,80) or RGB(0,32,96). You will get a brighter color instead of a lighter one. But on most colors, it works great!

Solution 3: RGB to HSV Manipulation

So far this code doesn’t work as well with certain colors as the first solution, but I am posting it in hopes that someone can save it.

The idea behind this is to convert the RGB color code into an HSV (hue, saturation, brightness value) code. Once in HSV, you can specifically target the “V” value while maintaining the hue and saturation, essentially allowing you to maintain the base color. Once you manipulated the HSV color code, you translate it back to RGB and apply it to your cell fill.

I pulled this logic together straight from a great article entitled .

Any Other Ideas?

There were lots of algorithms floating around in many different coding languages. Let me know if you have a better or more efficient way of lightening or darkening a fill color while maintaining the essence of the color. I know there are simple ways to adjust the brightness of an RGB code by applying a percent to it, but I don’t think the color outputs are visually appealing. I looking forward to your responses!

Hidden Hacks For VBA Macro Coding

After 10+ years of creating macros and developing add-ins, I’ve compiled all the hacks I wish I had known years ago!

Learn These Hacks Now For Free!

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.

Создание условного форматирования для ячеек в VBA Excel

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

Шаг 1: Откройте VBA редактор

Первым шагом для создания условного форматирования ячеек в VBA Excel является открытие VBA редактора. Для этого можно использовать горячую клавишу ALT + F11 или выбрать «Разработчик» → «Визуально-базисный редактор» в меню Excel.

Шаг 2: Выберите лист и ячейку

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

Шаг 3: Создайте новое правило форматирования

Для создания условного форматирования в VBA Excel нам нужно создать новое правило форматирования. Мы можем сделать это, используя свойство «FormatConditions» объекта «Range».

Шаг 4: Установите условное форматирование

После создания нового правила форматирования, нам нужно установить желаемое форматирование для ячейки при выполнении условия. Мы можем сделать это с помощью свойств «Font», «Interior» или «Borders» для объекта «FormatCondition».

В этом примере мы установили жирный шрифт и красный цвет для текста в ячейке, если значение ячейки «A1» равно 10.

Шаг 5: Примените условное форматирование

Наконец, мы должны применить созданное условное форматирование к ячейке, используя свойство «AppliesTo» для объекта «FormatCondition».

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

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

Фильтр и сортировка ячеек по цвету в Excel

Рассмотрим основные способы фильтрации и сортировки данных по цвету (как по заливке ячейки, так и по заливке текста) в Excel.

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

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

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

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

• Автофильтр и инструмент «Настраиваемая сортировка» (доступен начиная с версии Excel 2007);
• Применение пользовательских функций.