Is it possible to paint the background in a vba function. Cell color in Excel

Sometimes it becomes necessary to change, determine the color of the cell to be copied, or paint over a certain area, thereby giving unique and expressive features to the data table.

In this article we will look at how you can manually change the color of a cell, as well as how to change the color of a range of cells or one selected cell in VBA.

Let's start with something simple. On the main toolbar of the ribbon is the Format Cells panel:

In order to change the color of a cell (range of cells), we need to select it, and then select the required color in the Toolbar. You can also set other colors by selecting them from the palette. The toolbar also changes the text color, font size and cell border format.

Now let's set the cell format using context menu, to do this, right-click on the cell and select “Format Cells” in the list that opens:

On the Fill tab, you can choose the background color and pattern.

Let's consider a slightly different situation. Let's say you want to copy the cell color (and format) from an existing one and apply it to your cells. Let’s use the button on the main panel “Format by sample” (“whisk”):

Thus, in order to copy the format, we need to select the cell we are interested in, click on the “broom” and click on the cell whose format we want to set.

Similar operations can be described in Macros. If there is a need to insert a condition into the code according to which the cell format will change or the cells will be summed with a certain color or font, then after copying the format and recording a macro, you can see that:

Set the cell color (A1 is colored Yellow):

Sub Macro2() Range("A1").Select With Selection.Interior .Color = 65535 End With End Sub

Copy cell format (A1 format is copied to A3):

Sub Macro1() Range("A1").Select Selection.Copy Range("A3").Select Selection.PasteSpecial Application.CutCopyMode = False End Sub

Now, by combining the format with conditional operators, you can write calculations (for example, summation) based on the color condition.

We will be grateful if you click +1 and/or I like at the bottom of this article or share with your friends using the buttons below.

Filling a cell with color VBA Excel. Cell background. Properties.Interior.Color and.Interior.ColorIndex. Color RGB model. Standard palette of 56 colors. Predefined constants.

The.Interior.Color property of the Range object

Beginning with Excel 2007, the primary way to fill a range or individual cell with color (paint, add, change the background) is to use the Interior.Color property of the Range object by assigning it a color value as a decimal number from 0 to 16777215 (16777216 colors in total).

Filling a cell with color in VBA Excel

Code example 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

Place example code in your software module and press the button on the “Run Sub” toolbar or on the keyboard “F5”, the cursor should be inside the running program. On the active sheet Excel cells and the range selected in the code will appear in the appropriate colors.

There is one interesting nuance: if assigned to a property .Interior.Color negative value from -16777215 to -1, then the color will correspond to the value, equal to the amount maximum palette value (16777215) and assigned negative value. For example, the fill of all three cells after executing the following code will be the same:

Sub ColorTest11() Cells(1, 1).Interior.Color = -12207890 Cells(2, 1).Interior.Color = 16777215 + (-12207890) Cells(3, 1).Interior.Color = 4569325 End Sub

Tested in Excel 2016.

Displaying messages about numeric color values

It is impossible to remember the numeric values ​​of colors, so the question often arises of how to find out the numeric value of a cell’s background. The following VBA Excel code displays messages about the numeric values ​​of the previously assigned colors.

Code example 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

Instead of displaying messages, you can assign numeric values colors to variables by declaring them as Long.

Using Predefined Constants

VBA Excel has predefined constants for commonly used cell fill colors:

The color is assigned to a cell by a predefined constant in VBA Excel in the same way as with a numeric value:

Code example 3:

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

RGB color model

The RGB color system is a combination of three primary colors of varying intensity: red, green and blue. They can take values ​​from 0 to 255. If all values ​​are 0, it is black, if all values ​​are 255, it is white.

You can select a color and find out its RGB values ​​using the Excel palette:

Before you can assign a color to a cell or range using RGB values, they must be converted to decimal number, indicating color. There is a VBA Excel function for this, which is called RGB.

Code example 4:

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

Property.Interior.ColorIndex of Range object

Before advent of Excel 2007 there was only a limited palette for filling cells with background, consisting of 56 colors, which has been preserved to this day. Each color in this palette is assigned an index from 1 to 56. You can assign a color to a cell by index or display a message about it using the.Interior.ColorIndex property:

Code example 5:

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

You can view a limited palette for filling cells with background by running in VBA Excel is the simplest macro:

Code example 6:

Sub ColorIndex() Dim i As Byte For i = 1 To 56 Cells(i, 1).Interior.ColorIndex = i Next End Sub

The row numbers of the active sheet from 1 to 56 will correspond to the color index, and the cell in the first column will be filled with a background corresponding to the index.

You can see the finished standard palette of 56 colors.

Textual Sub Procedure_1() "Create in random access memory computer "named area - array "myColor". "The array consists of three lines and two columns. "If you need to look for more three words , then you need to change “the number of rows here: make it not 1 to 3, but for example 1 to 4.” The number of columns does not need to be changed. 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. Place "myColor" in the first array column text, "which will be searched in Excel cells. "In the second column we place the number of the color that needs to be "applied. "The color number can be found out like this: "1) go to Excel; "2) make active the cell for which you want to find out the color; "3) go to VBA; "4) View - Immediate Window. A window will open. In this window, paste “this code and press the “Enter” key. The color number will be obtained. “print activecell.Interior.Color” 5) There is also “ColorIndex”.. In the help in VBA (Excel 2010), if you “insert the text “ColorIndex” in the upper right corner and press the “Enter” key, a list of articles will appear. "Select the "ColorIndex Property" article. It shows "which color is assigned which number."I put the numbers in quotes because I gave the array the data type "Text". 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. For the convenience of writing code, we give the name “rngSearch” to the range of cells where “you need to paint over the cells. Through this name we will access “the desired range of cells.<>Set rngSearch = ActiveSheet.Range("A1:D25") "Using a loop with "i" we look through all the rows in the "myColor" array. "UBound(myColor, 1) is the ordinal number

last line in the "myColor" array. For i = 1 To UBound(myColor, 1) Step 1 "3. It is faster to search instead of viewing each cell. "LookAt:=xlWhole - a complete match is searched.

The.Interior.Color property of the Range object

Set rngFind = rngSearch.Find(What:=myColor(i, 1), LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) " If found, the cell containing the found text is given the name "rngFind". And through this name you can access the cell.

Filling a cell with color in VBA Excel

Code example 1:

Place the example code in your program module and press the button on the "Run Sub" toolbar or on the "F5" keyboard, the cursor should be inside the executing program. On active Excel sheet The cells and range selected in the code will appear in the appropriate colors.

There is one interesting nuance: if assigned to a property .Interior.Color a negative value from -16777215 to -1, then the color will correspond to a value equal to the sum of the maximum palette value (16777215) and the assigned negative value. For example, the fill of all three cells after executing the following code will be the same:

Instead of displaying messages, you can assign numeric color values ​​to variables by declaring them as Long.

Using Predefined Constants

VBA Excel has predefined constants for commonly used cell fill colors:

The color is assigned to a cell by a predefined constant in VBA Excel in the same way as with a numeric value:

Code example 3:

RGB color model

The RGB color system is a combination of three primary colors of varying intensity: red, green and blue. They can take values ​​from 0 to 255. If all values ​​are 0, it is black, if all values ​​are 255, it is white.

You can select a color and find out its RGB values ​​using the Excel palette:

Before you can assign a color to a cell or range using RGB values, they must be converted to a decimal number that represents the color. There is a VBA Excel function for this, which is called RGB.

Code example 4:

Property.Interior.ColorIndex of Range object

Before the advent of Excel 2007, there was only a limited palette for filling cells with backgrounds, consisting of 56 colors, which has survived to this day. Each color in this palette is assigned an index from 1 to 56. You can assign a color to a cell by index or display a message about it using the.Interior.ColorIndex property.

Cell background color in Microsoft Office Excel determined by the ColorIndex property of this cell's Interior object.

For example, the color of cell A1 can be set like this: Cells(1, 1).Interior.ColorIndex = 6 (yellowish)
Accordingly, it is possible to calculate the color of a cell like this: ColorVar = Cells(1, 1).Interior.ColorIndex

The most common sequence of actions for understanding this example is the following.

1. Open Excel.

2. Press Alt + F11

3. On the right, enter the following code:

Private Sub Worksheet-Activate()
Cells(1, 1).Interior.ColorIndex = 6
End Sub

4. Switch back to Excel.

5. Switch to the 2nd sheet, and then again to the 1st. As a result, the background color of the first cell on the first sheet should become yellowish.

The color of the text is not specified in any format (RGB, CMYK, etc.), but by the number under which it is located in the Excel gamma. In total, there are 55 colors in the range and one value is assigned to autoflowering. In total, using the ColorIndex() characteristic, it is possible to set 56 color values. These 55 colors are in the Colors collection of the ActiveWorkbook object, so you can change them if you want.