Vba excel как изменить цвет текста - Исправление ошибок и поиск оптимальных решений проблем

Изменение цвета текста (шрифта) в ячейке рабочего листа Excel с помощью кода VBA. Свойства ячейки (диапазона) .Font.Color, .Font.ColorIndex и .Font.TintAndShade.

Использование цветовой палитры для присвоения цвета тексту в ячейке листа Excel аналогично присвоению цвета фону ячейки, только свойство диапазона .Interior меняем на свойство .Font.

Цвет текста и предопределенные константы

Цвет шрифту в ячейке можно присвоить с помощью предопределенных констант:

Range(«A1:C3»).Font.Color = vbGreen

Range(Cells(4, 1), Cells(6, 3)).Font.Color = vbBlue

Cells(7, 1).Font.Color = vbRed

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

Цвет шрифта и модель RGB

Для изменения цвета текста в ячейке можно использовать цветовую модель RGB:

Range(«A1»).Font.Color = RGB(200, 150, 250)

Cells(2, 1).Font.Color = RGB(200, 150, 100)

Аргументы функции RGB могут принимать значения от 0 до 255. Если все аргументы равны 0, цвет — черный, если все аргументы равны 255, цвет — белый. Функция RGB преобразует числовые значения основных цветов (красного, зеленого и синего) в индекс основной палитры.

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

Свойство .Font.ColorIndex

Свойство .Font.ColorIndex может принимать значения от 1 до 56. Это стандартная ограниченная палитра, которая существовала до Excel 2007 и используется до сих пор. Посмотрите примеры:

Range(«A1:D6»).Font.ColorIndex = 5

Cells(1, 6).Font.ColorIndex = 12

Таблица соответствия значений ограниченной палитры цвету:

Стандартная палитра Excel из 56 цветов

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

Свойство .Font.ThemeColor

Свойство .Font.ThemeColor может принимать числовые или текстовые значения констант из коллекции MsoThemeColorIndex:

Range(«A1»).Font.ThemeColor = msoThemeColorHyperlink

Cells(2, 1).Font.ThemeColor = msoThemeColorAccent4

Основная палитра

Основная палитра, начиная c Excel 2007, состоит из 16777216 цветов. Свойство .Font.Color может принимать значения от 0 до 16777215, причем 0 соответствует черному цвету, а 16777215 — белому.

Cells(1, 1).Font.Color = 0

Cells(2, 1).Font.Color = 6777215

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

Отрицательные значения свойства .Font.Color

При записи в Excel макрорекордером макроса с присвоением шрифту цвета используются отрицательные значения свойства .Font.Color, которые могут быть в пределах от -16777215 до -1. Отрицательные значения соответствуют по цвету положительному значению, равному сумме наибольшего индекса основной палитры и данного отрицательного значения. Например, отрицательное значение -8257985 соответствует положительному значению 8519230, являющегося результатом выражения 16777215 + (-8257985). Цвета текста двух ячеек из следующего кода будут одинаковы:

Cells(1, 1).Font.Color = —8257985

Cells(2, 1).Font.Color = 8519230

Свойство .Font.TintAndShade

Еще при записи макроса с присвоением шрифту цвета макрорекордером добавляется свойство .Font.TintAndShade, которое осветляет или затемняет цвет и принимает следующие значения:

-1 — затемненный;
0 — нейтральный;
1 — осветленный.

При тестировании этого свойства в Excel 2016, сравнивая затемненные и осветленные цвета, разницы не заметил. Сравните сами:

Sub Test()

With Range(Cells(1, 1), Cells(3, 1))

.Value = «Сравниваем оттенки»

.Font.Color = 37985

End With

Cells(1, 1).Font.TintAndShade = —1

Cells(2, 1).Font.TintAndShade = 0

Cells(3, 1).Font.TintAndShade = 1

End Sub

В первые три ячейки первого столбца записывается одинаковый текст для удобства сравнения оттенков.

Разноцветный текст в ячейке

Отдельным частям текста в ячейке можно присвоить разные цвета. Для этого используется свойство Range.Characters:

Sub Test()

With Range(«A1»)

.Font.Color = vbBlack

.Value = «Океан — Солнце — Оазис»

.Font.Size = 30

.Characters(1, 5).Font.Color = vbBlue

.Characters(9, 6).Font.Color = RGB(255, 230, 0)

.Characters(18, 5).Font.Color = RGB(119, 221, 119)

End With

End Sub

Результат работы кода:

Источник

Цвет шрифта Excel VBA

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

Когда я подготовить дашборд в excelПанель инструментов в Excel — это расширенный инструмент визуализации, который предоставляет обзор важнейших показателей и точек данных бизнеса. Преобразовывая необработанные данные в содержательную информацию, информационная панель упрощает процесс принятия решений и анализа данных.читать далее, я обычно трачу значительное количество времени на форматирование ячеек, шрифтов и т. д. Часто я чувствую себя косметологом Excel, глядя на различные цвета форматирования Excel. Изменение цвета шрифта на листе Excel — простая задача, но когда дело доходит до Excel, вы должны знать, как это сделать. написание кода VBAКод VBA относится к набору инструкций, написанных пользователем на языке программирования приложений Visual Basic в редакторе Visual Basic (VBE) для выполнения определенной задачи.читать далее изменить цвет шрифта.

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

Диапазон («A1:A10»)

Затем нам нужно выбрать свойство FONT.

Диапазон («A1:A10»). Шрифт

Тогда что мы хотим сделать с этим шрифтом, так что выберите Color?

Диапазон («A1:A10»). Шрифт. Цвет

Таким образом, нам нужно создать код для изменения цвета шрифта. Это выглядит сложно, не так ли?

Но помните, вначале все кажется сложным, потом вы освоитесь.

Вы можете использовать это изображение на своем веб-сайте, в шаблонах и т. д. Пожалуйста, предоставьте нам ссылку на авторствоСсылка на статью должна быть гиперссылкой
Например:
Источник: Цвет шрифта VBA (wallstreetmojo.com)

Как изменить цвет шрифта с помощью VBA?

Вы можете скачать этот шаблон Excel цвета шрифта VBA здесь — Цвет шрифта VBA Шаблон Excel

Пример №1 – Использование цветового индекса

Свойство Color Index отличается от свойства Свойство цвета в VBAИндекс цвета VBA используется для изменения цвета ячеек или диапазонов ячеек. Эта функция имеет уникальную идентификацию для различных типов цветов.читать далее. Используя числовые значения, мы можем изменить цвет ячеек, шрифты.

Числа варьируются от 1 до 56, и каждое число представляет разные цвета. Ниже приведен список чисел и их цветов.

Хорошо, давайте проверим это.

У меня есть значение в ячейке A1.

Я хочу изменить цвет шрифта ячейки A1 на зеленый. Ниже приведен код.

Код:

Sub FontColor_Example1()

  Range("A1").Font.ColorIndex = 10

End Sub

Это изменит цвет шрифта ячейки A1 на зеленый.

Мы также можем использовать свойство CELLS, чтобы изменить цвет шрифта.

Код:

Sub FontColor_Example1()

  Cells(1, 1).Font.ColorIndex = 10

End Sub

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

Пример №2 – Использование свойства цвета

Цветовой индекс имеет очень ограниченное количество цветов от 1 до 56, но с помощью свойства COLOR мы можем использовать 8 встроенных цветов. vbЧерный, vbRed, vbЗеленый, vbСиний, vbжелтый, vbПурпурный, vbCyan, vbБелый.

Для этих цветов нам не нужно указывать какие-либо числа. Скорее мы можем просто получить к ним доступ, используя их имя, как показано выше. Ниже приведен пример кода для всех 8 цветов.

Код:

Sub vbBlack_Example()

  Range("A1").Font.Color = vbBlack

End Sub

Код:

Sub vbRed_Example()

  Range("A1").Font.Color = vbRed

End Sub

Код:

Sub vbGreen_Example()

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

End Sub

Код:

Sub vbBlue_Example()

  Range("A1").Font.Color = vbBlue

End Sub

Код:

Sub vbYellow_Example()

  Range("A1").Font.Color = vbYellow

End Sub

Код:

Sub vbMagenta_Example()

  Range("A1").Font.Color = vbMagenta

End Sub

Код:

Sub vbCyan_Example()

  Range("A1").Font.Color = vbCyan

End Sub

Код:

Sub vbWhite_Example()

  Range("A1").Font.Color = vbWhite

End Sub

Пример №3 – Использование свойства цвета с функцией RGB

Как мы видели, у нас есть только 8 встроенных цветов для работы. Но чтобы иметь большое разнообразие цветов, нам нужно использовать функцию под названием RGB. Помимо встроенных цветов, мы можем создавать собственные цвета, используя VBA RGBФункция RGB используется для получения числового значения значения цвета. Эта функция содержит три компонента именованного диапазона: красный, синий и зеленый. Другие цвета считаются компонентами этих трех разных цветов в VBA.читать далее функция.

Посмотрите на синтаксис функции RGB.

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

RGB означает «красный, зеленый и синий». Чтобы построить цвета, нам нужно указать числа от 0 до 255 для каждого цвета.

Ниже приведены несколько примеров для вас.

Ниже приведены некоторые примеры кода макроса.

Код:

Sub RGB_Example()

  Range("A1").Font.Color = RGB(0, 0, 0)
  'Change the font colour to black
End Sub

Код:

Sub RGB_Example()

  Range("A1").Font.Color = RGB(16, 185, 199)
  'Font color will be this
End Sub

Код:

Sub RGB_Example()

  Range("A1").Font.Color = RGB(106, 15, 19)
  'Font color will be this
End Sub

Код:

Sub RGB_Example()

  Range("A1").Font.Color = RGB(216, 55, 19)
  'Font color will be this
End Sub

УЗНАТЬ БОЛЬШЕ >>

Post Views: 871

Источник

Return to VBA Code Examples

In this Article

VBA Cell Font
Change Font Color
- vbColor
- Color – RGB
- ColorIndex
Font Size
Bold Font
Font Name
Cell Style

VBA Cell Font

In VBA, you can change font properties using the VBA Font Property of the Range Object. Type the following code into the VBA Editor and you’ll see a list of all the options available:

Range("A1).Font.

We will discuss a few of the most common properties below.

Change Font Color

There are a few ways to set font colors.

vbColor

The easiest way to set colors is with vbColors:

Range("a1").Font.Color = vbRed

However, you’re very limited in terms of colors available. These are the only options available:

Color – RGB

You can also set colors based on RGB (Red Green Blue). Here you enter color values between 0-255 for Red, Green, and Blue. Using those three colors you can make any color:

Range("a1").Font.Color = RGB(255,255,0)

ColorIndex

VBA / Excel also has a ColorIndex property. This makes pre-built colors available to you. However, they’re stored as Index numbers, which makes it hard to know what color is what:

Range("a1").Font.ColorIndex = …..

We wrote an article about VBA Color codes, including a list of the VBA ColorIndex codes. There you can learn more about colors.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro — A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

Learn More

Bold Font

It is easy to set a cell font to Bold:

Range("A1").Font.Bold = True

or to clear Bold formatting:

Range("A1").Font.Bold = False

Font Name

To change a font name use the Name property:

Range("A1").Font.Name = "Calibri"

Range("A1").Font.Name = "Arial"

Range("A1").Font.Name = "Times New Roman"

Cell Style

Excel offers the ability to create Cell “Styles”. Styles can be found in the Home Ribbon > Styles:

Styles allow you to save your desired Cell Formatting. Then assign that style to a new cell and all of the cell formatting is instantly applied. Including Font size, cell color, cell protections status, and anything else available from the Cell Formatting Menu:

Personally, for many of the models that I work on, I usually create an “Input” cell style:

Range("a1").Style = "Input"

By using styles you can also easily identify cell types on your worksheet. The example below will loop through all the cells in the worksheet and change any cell with Style = “Input” to “InputLocked”:

Dim Cell as Range

For Each Cell in ActiveSheet.Cells
  If Cell.Style = "Input" then
    Cell.Style = "InputLocked"
  End If
Next Cell

Источник

Written by Puneet for Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel for Mac

Key Notes

To make changes in a font, you need to use the VBA Font object.
There is a total of 18 properties with the font object that you can access and make changes.

VBA Font Object

In VBA, there is a font object which you can use to change properties of the font from a cell, like, font color, font size, font type, and you can also apply bold and italic to the font.

Syntax

expression.font

To use it, first, you need to define the cell address, which you can specify in the following ways.

Selection.Font
Range("A1").Font
Cells(1, 1).Font
Range("A1:A5").Font

Helpful Links: Run a Macro – Macro Recorder – Visual Basic Editor – Personal Macro Workbook

To change the color of the font, you have two different ways:

1. Using Color Constants

Excel has a few color constants that you can use to apply color to the font. For example, if you want to apply the red color to the font in cell A1, the code would be like the below:

Range("A1").Font.Color = vbRed

In the above code, after the font object, color is the property and you have used the vbRed constant that tells VBA to apply the red color to the cell A1. There is a total of eight constants that you can use:

vbBlack: Black
vbRed: Red
vbGreen: Green
vbYellow: Yellow
vbBlue: Blue
vbMagenta: Magenta
vbCyan: Cyan
vbWhite: White

2. Using RGB

You can also use the RGB color code to apply color to the font. RGB is the combination of red, green, and blue colors, where you can create a custom color using the code. Let’s say if you want to apply a combination of green and blue color to cell A1 the code would be:

Range("A1").Font.Color = RGB(0, 255, 255)

VBA Font Size

Font object also gives you access to the size property of the font. Let’s say you want to apply the font size of 16 to the font in the cell A1, the code would be:

Range("A1").Font.Size = 16

If you want to apply font size to all cells in a worksheet you can use the following code:

Cells.Font.Size = 16

And if only want to apply font size to cells where you have data, the code would be:

ActiveSheet.UsedRange.Font.Size = 16

Or to the selected cell.

Selection.Font.Size = 16

VBA Font Name

In the same way, you can also change the font name using the name property of the font object. Let’s say you want to apply the “Consolas” font the cell A1. The code would be:

Range("A1").Font.Name = "Consolas"

While using this property, you need to type the correct name of the font that you want to apply, and if somehow the name is incorrect, it won’t show you an error.

VBA Font Bold, Italic, and Underline

There are also properties that you can use to make the font bold, italic, and underline. Below are the codes that you need to write for this.

Range("A1").Font.Bold = True
Range("A1").Font.Italic = True
Range("A1").Font.Underline = True

With these properties, you need to define TRUE or FALSE. So if the font is already bold or italic and you want to remove it, then you need to use FALSE to remove them.

Other Useful Font Properties

Here add a few more properties that can be useful for you (Strikethrough, Subscript, and Superscript).

Range("A1").Font.Strikethrough = True
Range("A1").Font.Subscript = True
Range("A1").Font.Superscript = True

Источник

VBA Font Color

VBA has a lot of commands and functions to play with. We can do anything in VBA and apply that to Excel. Applying VBA in Excel is the easiest and a fun thing. VBA also has the function by which we can change the color of cell, fonts and we can even bold the characters as well. VBA font color is used in different ways and it helps to change the color of the fonts in excel.

How to Color a Font Using VBA?

Let’s see the examples of font color in Excel VBA.

You can download this VBA Font Color Excel Template here – VBA Font Color Excel Template

Example #1 – VBA Font Color

We have sample text in an Excel sheet with the text “VBA Font Color” in cell B3 as shown below. As we can see the color of the font is default black color in nature.

To apply the VBA code for changing the color of fonts for above-shown text, we need a module.

Step 1: So for this go to the VBA window and click on Module option available in the Insert menu option as shown below.

Step 2: Once we do that, we will get the blank window of Module. In that, start writing subcategory of VBA Font Color or in any other name as per your need as shown below.

Code:

Sub VBAFontColor2()

End Sub

Step 3: First select the range of the cell where the text is located. Here, our range will be cell B3 and that will be written followed by “.Select” command as shown below.

Code:

Sub VBAFontColor2()

Range("B3").Select

End Sub

Step 4: As we need to change the color of fonts so we will select the Font command with the help of Selection as shown below.

Code:

Sub VBAFontColor2()

Range("B3").Select
Selection.Font

End Sub

Step 5: After that, we will select the command Color separated by a dot (.) as shown below.

Code:

Sub VBAFontColor2()

Range("B3").Select
Selection.Font.Color

End Sub

Now to understand the formation of any color, in VBA we have RGB i.e. Red-Green-Blue. Numeric values of each color range from 0 to 255. Suppose we need to change to a color font to Black then RGB value will be RGB(0, 0, 0). As we already have the black color font we will try to select some other color.

Step 6: Let’s give the largest value to Green color and least to Red and zero to Blue color. As per that, considering Red at 20, Green at 230 and Blue at zero as shown below.

Code:

Sub VBAFontColor2()

Range("B3").Select
Selection.Font.Color = RGB(20, 230, 0)

End Sub

Step 7: Now compile the code to find if it has any error and then run by clicking on the play button located below the menu bar. We will apply font color for text which is in cell B3 and now changed from Black to Green color.

Example #2 – VBA Font Color

There is another way of changing the font color in VBA. For this, we will consider the same text as shown in example-1 located in cell B3.

Apart from RGB, we can change the color of fonts with the keyword “vb” followed by the name of the color. But by this process, we can only get the main basic color as the font color. Where with the help of RGB we could get color of any shade, just by putting the different values for Red, Green and Blue colors.

The color which can be used with vb are Black, Blue, Cyan, Green, Magenta, Red, White, and Yellow. And how to select the color in format is shown below.

Step 1: To apply this, go to VBA to create the subcategory in the name of VBA Font Color or in any other name in a new module as shown below.

Code:

Sub VBAFontColor3()

End Sub

Step 2: Select the range of cell for which we need to change the font color as shown below.

Code:

Sub VBAFontColor3()

Range("B3").Select

End Sub

Step 3: In the same manner what we have seen in example-1, use selection function with Font and Color to activate them.

Code:

Sub VBAFontColor3()

Range("B3").Select
Selection.Font.Color

End Sub

Step 4: It lets the color of font from Black to Cyan. For this select Cyan color by vbCyan as shown below.

Code:

Sub VBAFontColor3()

Range("B3").Select
Selection.Font.Color = vbCyan

End Sub

Step 5: If required then we can compile the code and then run it. We will see the font color of text at cell B3 is changed from Black to Cyan.

Example #3 – VBA Font Color

Microsoft has defined a variety of color in different numbers. These are 56 in numbers. We can select any of the color code between to 1 to 56 to change the font color of any cell. These color codes are shown below.

Step 1: Now go to the VBA window and open a new module. In that write the Sub Category of VBA Font Color as shown below.

Code:

Sub VBAFontColor4()

End Sub

Step 2: For this example, we will select the same text as seen in the above examples. Now select the range of the cell which is B3 as shown below.

Code:

Sub VBAFontColor4()

Range("B3").Select

End Sub

Step 3: Now in the second line, select the Font function with Selection command.

Code:

Sub VBAFontColor4()

Range("B3").Select
Selection.Font.

End Sub

Step 4: To select and apply the above-shown color code, we need to select ColorIndex function instead of Color which we used in example 1 and 2.

Code:

Sub VBAFontColor4()

Range("B3").Select
Selection.Font.ColorIndex = 46

End Sub

And at last, select the color code which we need to see in a selected range of cell. Let’s select color code 46 which is used for Orange color.

Step 5: Now run the code to see the change. We will the color font at cell B3 is now changed from black to orange.

Pros of VBA Font Color

It is easy to implement.
With the help of RGB, we can change the color of any shade we want.
It helps in creating the dashboard where we need to show the different types of data in a different color with the help of VBA.

Things to Remember

It is always recommended to use RGB when we do not know the color code. By giving a different color range from 0 to 255 in RGB we can create any color from dark to bright shade of our choice.
Saving the file in Macro Enable Excel helps to use and visit the written code multiple times.
Although changing font color in excel is the easiest way to do, but automating this activity in huge set of work can save the time and it will avoid the chances when the file may get crash or hang.

Excel VBA Font Color

VBA Font Color property one may use to change the font color of Excel cells using VBA code. Using the color index and color property with the RGB function, we can change the font color in multiple ways.

When we prepare a dashboard in excelThe dashboard in excel is an enhanced visualization tool that provides an overview of the crucial metrics and data points of a business. By converting raw data into meaningful information, a dashboard eases the process of decision-making and data analysis.read more, we usually spend considerable time formatting cells, fonts, etc. Often, we feel like an Excel beautician by looking at the various colors of the Excel formatting. For example, changing the font color in an Excel worksheet is an easy job, but when it comes to Excel, you should know how to writing VBA codeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more to change the font color.

To change the font color, first, we need to identify what cells we are going to change.

Range (“A1:A10”)

Then we need to select the FONT property.

Range (“A1:A10”).Font

Then what do we want to do with this font? So, select color.

Range (“A1:A10”).Font.Color

Like this, we need to construct the code to change the font color. It does not look easy.

But remember, everything seems tough at the beginning. Later you will get the hang of it.

Excel VBA Font Color
- How to Change Font Color using VBA?
  - Example #1 – Using Color Index
  - Example #2 – Using Color Property
  - Example #3 – Using Color Property with RGB Function
- Recommended Articles

You are free to use this image on your website, templates, etc., Please provide us with an attribution linkArticle Link to be Hyperlinked
For eg:
Source: VBA Font Color (wallstreetmojo.com)

How to Change Font Color using VBA?

You can download this VBA Font Color Excel Template here – VBA Font Color Excel Template

Example #1 – Using Color Index

The Color Index property is different from the Color property in VBAVBA Colour Index is used to change the colours of cells or cell ranges. This function has unique identification for different types of colours.read more. Using numerical values, we can change the color of cells and fonts.

Numbers range from 1 to 56, and each number represents different colors. Below is the list of numbers and their colors.

Let us test this out.

We have a value in cell A1.

We want to change the color of the cell A1 font to green. Below is the code.

Code:

Sub FontColor_Example1()

  Range("A1").Font.ColorIndex = 10

End Sub

It will change the color of the cell A1 font to green.

We can also use the CELLS property to change the color of the font.

Code:

Sub FontColor_Example1()

  Cells(1, 1).Font.ColorIndex = 10

End Sub

Like this, we can use numbers 1 to 56 to apply the desired color to the font.

Example #2 – Using Color Property

Color Index has very limited colors from 1 to 56, but using the COLOR property, we can use 8 built-in colors: vbBlack, vbRed, vbGreen, vbBlue, vbYellow, vbMagenta, vbCyan, vbWhite.

For these colors, we do not need to supply any numbers. Rather, we can access them using their name, as shown above. Below is the example code for all 8 colors.

Code:

Sub vbBlack_Example()

  Range("A1").Font.Color = vbBlack

End Sub

Code:

Sub vbRed_Example()

  Range("A1").Font.Color = vbRed

End Sub

Code:

Sub vbGreen_Example()

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

End Sub

Code:

Sub vbBlue_Example()

  Range("A1").Font.Color = vbBlue

End Sub

Code:

Sub vbYellow_Example()

  Range("A1").Font.Color = vbYellow

End Sub

Code:

Sub vbMagenta_Example()

  Range("A1").Font.Color = vbMagenta

End Sub

Code:

Sub vbCyan_Example()

  Range("A1").Font.Color = vbCyan

End Sub

Code:

Sub vbWhite_Example()

  Range("A1").Font.Color = vbWhite

End Sub

Example #3 – Using Color Property with RGB Function

We have seen that we have only 8 built-in colors to work with. But we need to use the RGB function to have various colors. Besides built-in colors, we can create our colors by using the VBA VBA RGBRGB 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.read more function.

Look at the syntax of the RGB function.

RGB (Red, Green, Blue)

RGB stands for “Red, Green, and Blue.” Therefore, we need to supply numbers from 0 to 255 for each color to construct colors.

Below are a few examples for you.

Below are some of the macro code examples

Code:

Sub RGB_Example()

  Range("A1").Font.Color = RGB(0, 0, 0)
  'Change the font colour to black
End Sub

Code:

Sub RGB_Example()

  Range("A1").Font.Color = RGB(16, 185, 199)
  'Font color will be this
End Sub

Code:

Sub RGB_Example()

  Range("A1").Font.Color = RGB(106, 15, 19)
  'Font color will be this
End Sub

Code:

Sub RGB_Example()

  Range("A1").Font.Color = RGB(216, 55, 19)
  'Font color will be this
End Sub

The VBA UserForm Label Font Color Snippet Template/Structure

The following is the VBA UserForm label font color snippet template/structure I explain (step-by-step) in the Sections below.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-label-font-color/
UserFormObjectReference.LabelControlCodeName.ForeColor = NewForeColorValue

The example workbook has a UserForm with 2 controls:

A Label.
- This is the UserForm Label the VBA UserForm Label font color example macro I create (by following the step-by-step process below) works with. In other words: This is the UserForm Label where the example macro (when executed) sets the font color.
A Text Box.
- The VBA UserForm Label font color example macro I create (by following the step-by-step process below) sets the UserForm Label font color depending on the string in this UserForm Text Box.

Example Excel UserForm for VBA Label font color example macro

Step 1: Refer to UserForm Label

Refer to the UserForm Label whose font color you want to set.

In other words: Create a VBA expression that returns an ActiveX Label control (object) representing the UserForm Label whose font color you want to set.

Consider doing the following when referring to the UserForm Label you work with:

Store the procedure setting the UserForm Label font color inside the applicable UserForm class module.
Use:
- The Me keyword to refer to the applicable UserForm.
- The Label’s codename to refer to the applicable Label control (object) inside the applicable UserForm.
Find (and change) the Text Box’s codename ((Name) property) in the applicable Properties Window.

The basic expression structure/template to refer to the UserForm Label is as follows:

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-label-font-color/
UserFormObjectReference.LabelControlCodeName

Step 1 Example

Store the example procedure (setting the UserForm Label font color) inside the applicable UserForm class module.
Work with a With… End With block. The statements inside the With… End With block work with the UserForm.
Refer to the Label codenamed “UserFormLabelFontColor” inside the UserForm.
Work with the following VBA constructs to obtain an ActiveX Label control (object) representing this UserForm Label:
- The Me keyword: Me.
- The ActiveX Label’s codename: UserFormLabelFontColor.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-label-font-color/
With Me
    .UserFormLabelFontColor
End With

Step 2: Set the Value of the ForeColor Property

Do the following to set the applicable ActiveX Label’s font color with the ForeColor property:

(1) Start with the ActiveX Label control (object) reference you created in step #1:

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-label-font-color/
UserFormObjectReference.LabelControlCodeName

(2) Refer to the ForeColor property.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-label-font-color/
UserFormObjectReference.LabelControlCodeName.ForeColor

(3) Set the value of the ForeColor property.

The ForeColor property specifies the UserForm Label’s font color as a value of the Long data type representing the applicable red, green, and blue components.

Consider working with the RGB function to set the value of the of the ForeColor property by specifying the applicable red, green, and blue components.
If you don’t know how to work with the RGB function, I suggest you read my Excel VBA Font Color RGB Tutorial I link to in the Related Excel Macro and VBA Training Materials and Resources Section above.

You can (also) consider setting the ForeColor property to:

Any of VBA’s color constants:
- vbBlack.
- vbRed.
- vbGreen.
- vbYellow.
- vbBlue.
- vbMagenta.
- vbCyan.
- vbWhite.
Any of VBA’s system color constants:
- vbScrollBars: Scroll bar color.
- vbDesktop: Desktop color.
- vbActiveTitleBar: Color of the active window’s title bar.
- vbInactiveTitleBar: Color of an inactive window’s title bar.
- vbMenuBar: Menu background color.
- vbWindowBackground: Window background color.
- vbWindowFrame: Window frame color.
- vbMenuText: Color text in menus.
- vbWindowText: Color text in windows.
- vbTitleBarText: Color text in caption, size box, and scroll arrow.
- vbActiveBorder: Active window’s border color.
- vbInactiveBorder: Inactive window’s border color.
- vbApplicationWorkspace: Background color of multiple document interface applications.
- vbHighlight: Background color of items selected in a control.
- vbHighlightText: Text color of items selected in a control.
- vbButtonFace: Shading color on the face of command buttons.
- vbButtonShadow: Shading color on the edge of command buttons.
- vbGrayText: Disabled (greyed out) text.
- vbButtonText: Push button text color.
- vbInactiveCaptionText: Text color in inactive caption.
- vb3DHighlight: Highlight color for 3-D display elements (Button Highlight).
- vb3DDKShadow: Darkest shadow color for 3-D display elements (Button Dark Shadow).
- vb3DLight: Second lightest 3-D color (after vb3DHighlight; Button Light Shadow).
- vbInfoText: Tooltip text color.
- vbInfoBackground: Tooltip background color.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-label-font-color/
UserFormObjectReference.LabelControlCodeName.ForeColor = NewForeColorValue

Step 2 Example

I do the following to set the value of the ForeColor property:

(1) Work with an event handler procedure triggered by the Change event of the TextBox control (object). The Change event occurs when the Value property of the TextBox control (object) changes.

The codename of the Text Box control I refer to is “UserFormTextBoxFontColor”

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-label-font-color/
Private Sub UserFormTextBoxFontColor_Change()
    With Me
        .UserFormLabelFontColor
    End With
End Sub

(2) Work with the If… Then… Else statement to do the following:

Test whether the text in the Text Box’s edit region is “Excel VBA UserForm Label Font Color”.
Specify the value of the ForeColor property of the Label control (object):
- One way (if the tested condition is met); or
- Another way (if the tested condition isn’t met).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-label-font-color/
Private Sub UserFormTextBoxFontColor_Change()
    With Me
        If .UserFormTextBoxFontColor.Value = "Excel VBA UserForm Label Font Color" Then
            .UserFormLabelFontColor
        Else
            .UserFormLabelFontColor
        End If
    End With
End Sub

If the text in the Text Box’s edit region is “Excel VBA UserForm Label Font Color”, I use the RGB function to set the value of the ForeColor property of the Label control (ForeColor = RGB(0, 128, 55)). I specify the Red, Green, and Blue arguments of the RGB function as follows:

Red: 0.
Green: 128.
Blue: 55.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-label-font-color/
Private Sub UserFormTextBoxFontColor_Change()
    With Me
        If .UserFormTextBoxFontColor.Value = "Excel VBA UserForm Label Font Color" Then
            .UserFormLabelFontColor.ForeColor = RGB(0, 128, 55)
        Else
            .UserFormLabelFontColor
        End If
    End With
End Sub

If the text in the Text Box’s edit region isn’t “Excel VBA UserForm Label Font Color”, I set the value of the ForeColor property of the Label control to VBA’s vbBlack color constant (ForeColor = vbBlack).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vba-label-font-color/
Private Sub UserFormTextBoxFontColor_Change()
    With Me
        If .UserFormTextBoxFontColor.Value = "Excel VBA UserForm Label Font Color" Then
            .UserFormLabelFontColor.ForeColor = RGB(0, 128, 55)
        Else
            .UserFormLabelFontColor.ForeColor = vbBlack
        End If
    End With
End Sub

The full VBA UserForm Label font color example macro is as follows:

Private Sub UserFormTextBoxFontColor_Change()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/vba-label-font-color/
    
    'Step 1: Refer to this UserForm
    With Me
        
        'Do the following:
            '(1) Test whether the UserFormTextBoxFontColor Text Box in this UserForm contains "Excel VBA UserForm Label Font Color"
            '(2) Set the value of the ForeColor property of the UserFormLabelFontColor Label in this UserForm depending on whether (or not) the UserFormTextBoxFontColor Text Box contains "Excel VBA UserForm Label Font Color"
        If .UserFormTextBoxFontColor.Value = "Excel VBA UserForm Label Font Color" Then

            'Step 2 (option 1): Use the RGB function to set the value of the ForeColor property of the UserFormLabelFontColor Label. Specify the red, green, and blue components of the color as follows:
                'Red: 0
                'Green: 128
                'Blue: 55
            .UserFormLabelFontColor.ForeColor = RGB(0, 128, 55)
        Else

            'Step 2 (option 2): Set the value of the ForeColor property of the UserFormLabelFontColor Label to VBA's vbBlack color constant
            .UserFormLabelFontColor.ForeColor = vbBlack
        End If
    End With

End Sub

The following GIF illustrates the effects of executing the VBA UserForm Label font color example macro (when the Change event of the TextBox control occurs).

Excel VBA UserForm Label font color example

Download the VBA UserForm Label Font Color Example Workbook

This Excel VBA UserForm Label Font Color Tutorial is accompanied by an Excel workbook with the data and VBA code I use when describing the step-by-step process above. Get this example workbook (for free) by clicking the button below.

The following Excel Macro and VBA Tutorials may help you better understand and implement the contents above.

Tutorials about general macro and VBA constructs and structures:
- Tutorials for Beginners:
  - Excel Macros: Click here to open.
  - Excel VBA: Click here to open.
- Enable macros in Excel: Click here to open.
- Work with the Visual Basic Editor (VBE): Click here to open.
- Create Sub procedures: Click here to open.
- Refer to objects: Click here to open.
- Work with:
  - Properties: Click here to open.
  - Data types: Click here to open.
  - Functions: Click here to open.
  - The Select Case statement: Click here to open.
  - Events: Click here to open.
Tutorials with practical VBA applications and macro examples:
- Work with the Value property: Click here to open.
- Work with font characteristics: Click here to open.
- Create UserForms: Click here to open.

This Excel VBA UserForm Label Font Color Tutorial is part of a more comprehensive series of Excel VBA Font Color Tutorials.

Excel VBA Font Color Index in 2 Easy Steps: Click here to open.
Excel VBA Font Color RGB in 2 Easy Steps: Click here to open.
Excel VBA Font Color HEX in 5 Easy Steps: Click here to open.
Excel VBA Change Font Color Based on Cell Value in 4 Easy Steps: Click here to open.
Excel VBA Change Font Color for Part of Text in 8 Easy Steps: Click here to open.
Excel VBA Text Box Font Color in 2 Easy Steps: Click here to open.
Excel VBA ActiveX Text Box Font Color in 2 Easy Steps: Click here to open.
Excel VBA UserForm Text Box Font Color in 2 Easy Steps: Click here to open.
Excel VBA Chart Data Label Font Color in 4 Easy Steps: Click here to open.
Excel VBA ActiveX Label Font Color in 2 Easy Steps: Click here to open.

You can find more Excel and VBA Tutorials in the organized Tutorials Archive: Click here to visit the Archives.

If you want to learn how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:

Premium Courses at the Power Spreadsheets Academy: Click here to open.
Books at the Power Spreadsheets Library: Click here to open.
VBA Cheat Sheets: Click here to open.

Code generators.
An extensive code library.
The ability to create your own code library.
Advanced coding tools.

Источник

Цвет текста и предопределенные константы

Цвет шрифта и модель RGB

Свойство .Font.ColorIndex

Свойство .Font.ThemeColor

Основная палитра

Отрицательные значения свойства .Font.Color

Свойство .Font.TintAndShade

Разноцветный текст в ячейке

Цвет шрифта Excel VBA

Как изменить цвет шрифта с помощью VBA?

Пример №1 – Использование цветового индекса

Пример №2 – Использование свойства цвета

Пример №3 – Использование свойства цвета с функцией RGB

УЗНАТЬ БОЛЬШЕ >> Post Views: 871

VBA Cell Font

Change Font Color

vbColor

Color – RGB

ColorIndex

Font Size

VBA Coding Made Easy

Bold Font

Font Name

Cell Style

Key Notes

VBA Font Object

Syntax

1. Using Color Constants

2. Using RGB

VBA Font Size

VBA Font Name

VBA Font Bold, Italic, and Underline

Other Useful Font Properties

VBA Font Color

How to Color a Font Using VBA?

Example #1 – VBA Font Color

Example #2 – VBA Font Color

Example #3 – VBA Font Color

Pros of VBA Font Color

Things to Remember

Recommended Articles

Excel VBA Font Color

Table of contents

How to Change Font Color using VBA?

Example #1 – Using Color Index

Example #2 – Using Color Property

Example #3 – Using Color Property with RGB Function

Recommended Articles

The VBA UserForm Label Font Color Snippet Template/Structure

Step 1: Refer to UserForm Label

Step 1 Example

Step 2: Set the Value of the ForeColor Property

Step 2 Example

Download the VBA UserForm Label Font Color Example Workbook

УЗНАТЬ БОЛЬШЕ >>

Post Views: 871