Menu
homeWindows 7

How to change the color of a row in Excel depending on the value in the cell. VBA Excel. Cell color (fill, background). RGB color model

If a value in a cell meets a user-defined condition, you can use it to highlight that cell (for example, change its background). In this article we will go further - we will select the entire table row containing this cell.

Let in range A6:C16 There is a table with a list of works, deadlines and completion status (see example file).

Task 1 - text values

It is necessary to highlight the line containing the work of a certain status. For example, if the work has not started, then the line will be highlighted in red, if the work has not yet been completed, then in gray, and if completed, then in green. We will select lines using rules.

Solution1

Let's create a small table with job statuses in the range E6:E9 .

Select a range of cells A7:C17 , containing a list of works, and install it through the menu Home/ Fill color the fill background is red (we assume that all works are initially in the status Not started).

Make sure the range of cells is selected A7:C17 (A7 must be ). Call the menu command Conditional formatting / Create a rule / Use a formula to determine which cells to format.

  • in field " Format values ​​for which the following formula is true"you need to enter =$C7=$E$8 (in the cell E8 the value is found In progress). Please note the use of ;
  • press the button Format;
  • select tab Fill;
  • choose gray color ;
  • Click OK.

ATTENTION: Once again I draw attention to the formula =$C7=$E$8 . Typically users enter =$C$7=$E$8 , i.e. enter an extra dollar symbol.

You need to do similar steps to highlight work in the status Completed. The formula in this case will look like =$C7=$E$9 and set the fill color to green.

As a result, our table will take the following form.

To quickly extend Conditional Formatting rules to a new row in a table, select the cells of the new row ( A17:C17 ) and press . The rules will be copied to the line 17 tables.

Task2 - Dates

Let's assume that a log of employee attendance at scientific conferences is kept (see. sample sheet file Dates).

Unfortunately, the Date of Visit column is not sorted and you need to highlight the date of the first and last visit of each employee. For example, employee Kozlov went to the conference for the first time on July 24, 2009, and the last time on July 18, 2015.

First, let's create a formula for conditional formatting in columns B and E. If the formula returns TRUE, then the corresponding row will be highlighted, if FALSE, then it will not.

In column D created = MAX(($A7=$A$7:$A$16)*$B$7:$B$16)=$B7, which defines the maximum date for a specific employee.

Note: If you need to determine the maximum date regardless of the employee, then the formula will be significantly simplified =$B7=MAX($B$7:$B$16) and the array formula will not be needed.

Now let's select all the table cells without a header and create a rule. Let's copy the formula into the rule (you don't need to enter it as an array formula!).

Now let's assume that the column with dates has been sorted and you want to select rows whose visit dates fall within a certain range.

To do this, use the formula =AND($B23>$E$22,$B23<$E$23)

For cells E22 And E23 with boundary dates (highlighted in yellow) used $E$22 and $E$23. Because the reference to them should not change in the UV rules for all table cells.

For cell B22 mixed addressing $B23 is used, i.e. the reference to column B should not change (for this there is a $ sign before B), but the reference to the row should change depending on the table row (otherwise all date values ​​will be compared with the date from B23 ).

Thus, the UV rule for example for a cell A27 will look =AND($B27>$E$22,$B27<$E$23) , т.е. A27 will be highlighted because in this line the date from B27 falls within the specified range (for cells from column A, the selection will still be made depending on the contents of column B from the same row - this is the “magic” of mixed addressing $B23).

And for the cell B31 the UV rule will look like =AND($B31>$E$22,$B31<$E$23) , т.е. В31 не будет выделена, т.к. в этой строке дата из B31 does not fall within the specified range.

Filling a cell with color in VBA Excel. Cell background. Properties.Interior.Color and.Interior.ColorIndex. RGB color 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 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. In the active Excel worksheet, 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:

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 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:

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 a decimal number that represents the 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 the advent of Excel 2007, there was only a limited palette for filling cells with a background, 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:

Code example 5:

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

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

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.

Filling a layer with color is a simple and popular operation in Photoshop. This may need to be done when you need a plain background or to apply some shade over the image - toning, for example, using a layer with yellow color to create a sepia effect.

In order to make a layer completely one color, I counted 5 possible ways.

Methods for filling a layer with color

Method 1

Menu command Editing - Fill(Fill) , or pressing a combination of hot keys that duplicates this command:

Hotkey combination: Shift+F5

A dialog box will open Fill. In it, in the drop-down list Use select Color. The color selection palette will immediately open. Decide on a color and click OK.

Method 2

Using the Alt+Backspace key combination, the layer is filled with the main color set on the tool palette - .

Method 3 Fill Tool

From the toolbar, select Fill tool. Click on the layer and it will immediately be painted with the main color (see example above).

Filling works when there is nothing extra on the layer. Otherwise, the tool will paint only certain areas.

I remember when I first started learning Photoshop, I used exactly this method. The idea is that you simply paint over the layer with a brush, like Tom Sawyer painted a fence.

All of the above methods have one common drawback - in cases where it is necessary, the layer filled with color will not increase. The part of the canvas that was added will be painted over automatically with the background color.

Example. Initially, the layer was filled with yellow paint. After enlarging the canvas, a reddish color was added along the outline, in accordance with what was displayed on the background color indicator.

To prevent this, you need to use the following method.

Method 5 Fill layer

Run the command Layer - New Fill Layer(New Fill Layer) - Color. You can immediately click OK. After this, a fill color selection palette will appear. Make a choice.