VBA Excel. Cell color (fill, background). Untitled Document Exercises and Independent. Filling a cell with color in VBA Excel

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.

When working with tables, the values displayed in the table are of primary importance. But an important component is also its design. Some users consider this a minor factor and do not pay much attention to it. But in vain, because a beautifully designed table is an important condition for its better perception and understanding by users. Data visualization plays a particularly important role in this. For example, you can use visualization tools to color table cells based on their content. Let's find out how this can be done in Excel.

Of course, it's always nice to have a well-designed table in which the cells are colored different depending on the content. But this feature is especially relevant for large tables containing a significant amount of data. In this case, filling the cells with color will make it much easier for users to navigate this huge amount of information, since it, one might say, will already be structured.

You can try to color the sheet elements by hand, but again, if the table is large, this will take a significant amount of time. In addition, in such a data array, the human factor can play a role and errors will be made. Not to mention that the table can be dynamic and the data in it changes periodically, and massively. In this case, manually changing the color becomes unrealistic.

But there is a way out. For cells that contain dynamic (changing) values, conditional formatting is applied, and for statistical data, you can use the tool "Find and Replace".

Method 1: Conditional Formatting

Using conditional formatting, you can set certain value limits at which cells will be colored in one color or another. Coloring will be carried out automatically. If the cell value, due to a change, goes beyond the border, then this sheet element will be automatically repainted.

Let's see how this method works with a specific example. We have a table of enterprise income in which the data is broken down monthly. We need to highlight in different colors those elements in which the amount of income is less 400000 rubles, from 400000 before 500000 rubles and exceeds 500000 rubles

We highlight the column that contains information on the company’s income. Then move to the tab "Home". Click on the button "Conditional Formatting", which is located on the ribbon in the toolbox "Styles". In the list that opens, select the item "Rule management...".

The window for managing conditional formatting rules opens. In field "Show formatting rules for" value must be set "Current fragment". By default, this is exactly what should be indicated there, but just in case, check and, in case of discrepancy, change the settings according to the above recommendations. After this, click on the button "Create a rule...".

The window for creating a formatting rule opens. In the list of rule types, select a position . In the rule description block in the first field, the switch should be in the position "Values". In the second field, set the switch to position "Less". In the third field we indicate the value; sheet elements containing a value less than which will be painted in a certain color. In our case this value will be 400000 . After that, click on the button "Format…".

The Format Cells window opens. Moving to the tab "Fill". Select the fill color that you want to highlight cells containing a value less than 400000 . After that, click on the button "OK" at the bottom of the window.

We return to the window for creating a formatting rule and click on the button there too "OK".

After this action we will again be redirected to Conditional Formatting Rules Manager. As you can see, one rule has already been added, but we need to add two more. So we press the button again "Create a rule...".

And again we find ourselves in the rule creation window. Moving to the section "Format only cells that contain". In the first field of this section we leave the parameter "Cell value", and in the second we set the switch to position "Between". In the third field you need to specify the initial value of the range in which the sheet elements will be formatted. In our case this number 400000 . In the fourth we indicate the final value of this range. It will amount to 500000 . After this, click on the button "Format…".

In the formatting window, move to the tab again "Fill", but this time we select a different color, and then click on the button "OK".

After returning to the rule creation window, also click on the button "OK".

As we see, in Rules Manager We have already created two rules. Thus, it remains to create the third. Click on the button "Create Rule".

In the rule creation window, again move to the section "Format only cells that contain". In the first field we leave the option "Cell value". In the second field, set the switch to police "More". In the third field we enter the number 500000 . Then, as in previous cases, click on the button "Format…".

In the window "Cell Format" again move to the tab "Fill". This time we choose a color that is different from the two previous cases. Click on the button "OK".

In the rule creation window, press the button again "OK".

Opens Rules Manager. As you can see, all three rules have been created, so click on the button "OK".

Now table elements are colored according to the specified conditions and boundaries in the conditional formatting settings.

If we change the contents in one of the cells, going beyond the boundaries of one of the specified rules, then this sheet element will automatically change color.

You can also use conditional formatting in a slightly different way to color worksheet elements.

Method 2: Using the Find and Select tool

If the table contains static data that is not planned to change over time, then you can use a tool to change the color of cells based on their contents called "Find and Select". This tool will allow you to find the specified values and change the color in these cells to the one desired by the user. But please note that when you change the content in the sheet elements, the color will not automatically change, but will remain the same. In order to change the color to the current one, you will have to repeat the procedure again. Therefore, this method is not optimal for tables with dynamic content.

Let's see how this works using a specific example, for which we will take the same table of enterprise income.

Select the column with the data that should be formatted in color. Then go to the tab "Home" and press the button "Find and Select", which is located on the ribbon in the toolbox "Editing". In the list that opens, click on the item "Find".

Window opens "Find and Replace" in the tab "Find". First of all, let's find the values up to 400000 rubles Since we do not have a single cell containing a value less than 300000 rubles, then, in fact, we need to select all elements that contain numbers in the range from 300000 before 400000 . Unfortunately, you cannot directly specify this range, as in the case of using conditional formatting, in this method.
But there is an opportunity to do things a little differently, which will give us the same result. You can specify the following pattern in the search bar "3?????". A question mark means any character. Thus, the program will search for all six-digit numbers that begin with the digit "3". That is, the search results will include values in the range 300000 – 400000 , which is what we need. If the table had smaller numbers 300000 or less 200000 , then for each range of one hundred thousand the search would have to be performed separately.

Enter the expression "3?????" in field "Find" and press the button "Find everything».

After that, search results open at the bottom of the window. Left-click on any of them. Then we type the key combination Ctrl+A. After this, all search results are selected and at the same time the elements in the column to which these results link are selected.

After the elements in the column are selected, do not rush to close the window "Find and Replace". While in the tab "Home" to which we moved earlier, go to the ribbon to the tool block "Font". Click on the triangle to the right of the button "Fill Color". A selection of different fill colors opens. We select the color that we want to apply to sheet elements containing values less than 400000 rubles

As you can see, all cells of the column containing values less than 400000 rubles, highlighted in the selected color.

Now we need to color the elements that contain values in the range from 400000 before 500000 rubles This range includes numbers that match the pattern "4??????". Enter it into the search field and click on the button "Find All", having previously selected the column we need.

Similarly with the previous time in the search results, we select the entire result obtained by pressing a combination of hot keys CTRL+A. After that, move to the fill color selection icon. We click on it and click on the icon of the shade we need, which will color the elements of the sheet, where the values are in the range from 400000 before 500000 .

As we can see, after this action, all table elements with data in the interval s 400000 By 500000 highlighted with the selected color.

Now we just have to select the last interval of values – more 500000 . Here we are also lucky, since all the numbers are more 500000 are in the range from 500000 before 600000 . Therefore, in the search field we enter the expression "5?????" and press the button "Find All". If there were values greater than 600000 , then we would have to additionally search for the expression “6?????” etc.

Again, highlight the search results using the combination Ctrl+A. Next, using the button on the ribbon, select a new color to fill the interval exceeding 500000 by the same analogy as we did earlier.

As you can see, after this action, all elements of the column will be filled in, according to the numerical value that is placed in them. Now you can close the search window by clicking the standard close button in the upper right corner of the window, since our problem can be considered solved.

But if we replace the number with another one that goes beyond the boundaries that are set for a specific color, then the color will not change, as was the case in the previous method. This indicates that this option will only work reliably in those tables in which the data does not change.

As you can see, there are two ways to color cells depending on the numeric values they contain: using conditional formatting and using the "Find and Replace". The first method is more progressive, as it allows you to more clearly define the conditions by which sheet elements will be highlighted. In addition, with conditional formatting, the color of an element automatically changes if the content in it changes, which the second method cannot do. However, filling cells depending on the value by using the tool "Find and Replace" It can also be used, but only in static tables.

Creating color. Filling areas.

In this chapter, we'll look at color selection, fill commands, and tools. The toolbar contains active color indicators. The top square is an indicator of the main color, the bottom square is an indicator of the background color.
The icon in the upper left corner (in the expanded panel) returns the default colors: black is the foreground color, white is the background color. A round arrow in the corner changes their places.
Color selection is done using the tool Pipette. If you click with this tool anywhere in the image, the primary color indicator will take on the color of the pixel you clicked on.
If you move over the image without releasing the left mouse button, the primary color indicator will change color all the time. In a scroll Size control panel sample mode is selected by default Spot sample. To enable averaging of the color of neighboring pixels, you need to select one of the lower lines: averaging over a 3 by 3 pixel square to 101 by 101 pixels.

To create a color, click on the primary color indicator and the Select Color window will appear on the screen. At the bottom right you can select the color creation method in the corresponding models: HSB, RGB, Lab, SMUK.

In the color picker using cnopki Custom Library you can access libraries of standardized spot and composite colors. The composition of each standard color is presented in models SMUK and Lab. You can use printed color charts and have an exact idea of how the color you choose will look when printed.

Exercise 7. 1. Creating Color

1. Open an image Basic colors.jpg from the folder Lesson 7.
2. This image (Fig. 7.1) shows the 6 primary colors of the RGB and SMUK models. As you can see, the same color has different shades depending on the color model (affiliation according to the inscriptions). In RGB they are brighter, unlike the SMUK model.

Figure 7.1. Primary colors in RGB and CMYK models

3. Let's check the composition of the color spots. To open the Select Color dialog box, click on the primary color indicator. Click the eyedropper on the color spots one by one (their composition may not be entirely accurate due to the file and color model being rewritten).
4. To select a spectrally pure green color in the model RGB set parameters: R = 0, G = 255, V = 0. Click OK. For comparison, set the color to pure green in the model SMUK the following parameters in the counters: C = 100%, M = 0%, Y = 100%, K = 0%.
5. In the square to the right of the scale, compare the shades of the created green colors. Create the rest of the primary colors in the same way.
6. Don't close it. Let's continue working.

Exercise 7.2. Color catalogs

You can also use palettes to select colors Color and Swatches. In the upper left corner of the palette you see the same active color indicators as in the toolbar.
Notice the black and white squares in the lower right corner of the palette. They allow you to select white and black colors if you do not want to reset the selected foreground and background colors in the toolbar.
The Swatches palette contains a selection of frequently used colors, where you can write down your favorite colors so that you always have them at hand. To select one of the catalog colors as the main one, you need to click on it.

1. Open the Swatches palette and place it on the free field.
2. Enter the palette menu, select sets of standard libraries sequentially: ANPA, HKS, PANTONE, TOUO, DIC, TRUMACH, WEB.
3. To see the entire catalog of standard colors, drag the palette down. To restore the default directory, select the menu bar Restore Samples.
4. Select a tool Pipette and click on your favorite color (from any image).
5. Move the pointer to the last line. The pointer will turn into a paint bucket. Click on an empty space. In the dialog box, enter the name of your favorite color and click OK.
6. This operation can also be performed using a similar window button Color selection.
7. Remove the created color from the Swatch. From the context menu, select Remove sample.
8. Close Primary colors without saving.

Exercise 7.3. Fill with color mixing

After we have already learned how to choose and create colors, we can begin to put this knowledge into practice. Let's look at the commands for filling and outlining areas. One thing to clarify is that a fill is the coloring of the inside of an area, and a stroke is the colored line around the area.
Filling areas is done with the command Edit ~ Fill and tools that we will look at later. The Fill command fills the entire selection with a color or pattern.
In the dialog box in the Use rollout, select a fill color or pattern: Main Color, Background Color, Color, Pattern, Black, 50% Gray, or White.
When you select a pattern fill, the scroll below is activated Custom Pattern, when opened, a palette of patterns appears. The Opacity field sets the transparency of the color.
In a scroll Mode There are many blending modes to choose from. They are also used for all drawing and adjustment tools. Almost all of these modes are also used to blend different layers of an image.
Different blending modes allow you to create subtle, sometimes unexpected, color effects. New paint can modify old paint according to a certain mixing algorithm.

2. Select a shade of blue as the main color and a shade of pink as the background color.
3. Create a rectangular selection using the appropriate tool and fill it with the main color.
4. Next, create an elliptical selection intersecting the rectangle, as shown in Fig. 7.2.

Figure 7.2. Fill with different color mixing modes

5. Call the fill command Edit ~ Fill and fill the elliptical area in different modes with the background pink color.
6. Before each new fill, you need to go back through the history to the “elliptical selection” line.
7. Once you've tested ALL the blending modes, you'll get a feel for their differences and the effects they produce.
8. Save your favorite mode under a name Blending mode -….

Exercise 7.4. Piglet with a tattoo

Tool Bucket with paint paints adjacent areas depending on the color of the specified pixel. The principle of operation resembles a magic wand (Tolerance parameter), and the rest of the settings do not differ from the window settings Filling.
The Opacity field in the control panel allows you to control the opacity. Scroll Mode controls the mixing modes of new and old paints.

1. Open a familiar document Piglet.jрg from the folder Lesson 5.
2. Let's decorate this pink creature. To do this, select the color for the tattoo according to your taste in the primary color selection window.
3. Draw a stripe with the Lasso tool.
4. Select a tool Bucket of paint, in the Control Panel, set the mode to Contiguous and click inside the selected area.
5. The strip will be colored - since the fill is limited to the selected area, we get one colored strip.
6. Next, create a new strip and color it in the same way. To enhance the effect, you can try different color mixing modes when painting.
7. As a result of your efforts Piglet can take on the war paint of a wild boar, for example, as shown in Fig. 7.3.
8. Save as a name Piglet with a tattoo.

Figure 7.3. Painting with the Paint Bucket Tool

Exercise 7.5. Gradient fill

Let's move on to more complex fills. A gradient fill looks very impressive, representing a smooth transition between two or more colors. Transparency can also be used instead of one of the colors.
A gradient transition between colors is created by the tool Gradient in the direction you specify by drawing the gradient vector with it. The orientation of the gradient depends on the direction of this vector, and its start and end points define the beginning and end of the color transition.
To draw a gradient vector, click at the starting point of the transition and drag the pointer to the end of it. The vector does not have to fit entirely inside the image; part of it, or even both ends, can extend beyond the edges of the image.
To select the desired fill type, click on the corresponding button on the control panel: l linear gradient, radial gradient, angular gradient, reflected gradient, rhombic gradient.
In field Opacity sets the degree of transparency of the gradient fill, and in the rollout Mode- color mixing mode.
The Swap checkbox swaps the fill colors by 180", If the Rasterize checkbox is enabled, then when creating a gradient, a color raster is used on the border of two shades, which prevents stripes from appearing in this place when printing. Checking the Transparency checkbox allows you to work with gradients that have transparent areas. If there is no selected area in the image, the fill fills the entire document, in the case of a selected area, only it is filled. Also, if there are several areas, the fill fills them all, as if they were one area.
Open the scroll next to the window with the current gradient in the control panel. A gradient palette will appear on the screen. Here are different styles of gradients, which differ from each other in the colors involved, the number and distribution of color transitions.
View the names of these gradients by hovering over them. You'll see that most of them use specific colors: Black White; Red Green; From main to background.
Several other names mention the word Transparent. These are gradients where transparency is used instead of one of the colors, usually white. To prevent these transparent areas from being replaced with white, you need to check the Transparency checkbox.

1. Create a new document with a white background.
2. Select the elliptical selection tool and fill the area with the appropriate gradient fill, as shown in Fig. 7.4. For the gradient style, select Black, White, and check the Invert icon. Fill the selected areas one by one, moving the selection to a new location.

Figure 7.4. Various types of gradient fill and gradient palette

3. Try highlighting the white background and filling it with a more attractive gradient style.
4. Save as a name Gradient fill and close.

Exercise 7.6. Creating a Patriotic Gradient

1. Select a toolGradientwith an open document.
2. Double-click the gradients rollout to open the gradient editor.
3. Select a gradient with the name in the palette Blue, red, yellow.
4. Through the color selection window, change the color of the blue marker to white, red to blue, yellow to red in the order in which these colors are located on the Russian flag.
5. To sharpen the edges, move the white marker to the location 33% to the right, move the blue color there too. We combine them, creating a sharp border between white and blue.
6. Similarly, create a blue-red border. Here you can use another marker.
7. As a result of your efforts, you will get a new gradient, as shown in Fig. 7.5. Save the new gradient with the appropriate name in the gradient palette.

Figure 7.5. New gradient in the form of the Russian flag

8. Fill the open document with a new gradient from top to bottom, selecting the linear fill type. Try also creating two more new gradients: with smooth borders and with transparent stripes.
9. Save as name Flag and close.

Exercise 7.7. Stroke and fill selection

You can outline the selected area using the command Edit - Stroke. In the dialog box, set the stroke thickness in the Thickness field. The Default Color field contains the base color. If you want to stroke with a different color, click on the color rectangle. Using the Color Selection window, you can specify any other color. In the Blending section there are options for mixing the old and new color Mode and Opacity. In the Position section, you can choose how the stroke should be positioned in relation to the border of the selected area. In Fig.

7.7. on the right, all three modes are used for the elliptical area: Inside

- the entire stroke is inside the selected area; Center

- the border of the area is in the center of the stroke; Outside

Let's do an exercise to create an outline using outlining a selected area.

1. Create a new document with a white background.

2. Using the rectangular and elliptical selection tools, create a composite shape (using Boolean operations) in the style of an Egyptian cross, or a more complex shape using a lasso (Figure 7.7).

3. To stroke, execute the command Edit ~ Stroke.

In the dialog box, set the thickness of the thin stroke.

4. Select the position of the outline relative to the selection. Click OK.

5. To fill the selection shown in Fig. 7.7, use the appropriate command.

Figure 7.7. Stroke and fill selection

Control questions:

How are areas filled?

What is Color Mixing Mode?

What is the purpose of the Gradient tool? How to create a new gradient?

Hello, dear readers. Have you ever worked with huge data in a table? You know, it will be much more convenient to work with them if you know how to highlight several Excel cells with different colors under a certain condition. Would you like to know how it's done? In this tutorial we will make the cell color change depending on the Excel value, and also color all the cells using search.

The fill color changes with the value

As an example, we will practice making a cell change color in a given table under a certain condition. Yes, not one, but all with a value in the range from 60 to 90. To do this, we will use the “Conditional Formatting” function.

First, select the data range that we will format.

Next, on the “Home” tab, find the “Conditional Formatting” button and select “Create Rule” from the list.

The “Create formatting rules” window has opened. In this window, select the type of rule: “Format only cells that contain.”

Next, go to the “Change the rule description” section, where you need to specify the conditions under which the filling will be performed. In this section you can set a variety of conditions under which it will change.

In our case, it is necessary to put the following: “cell values” and “between”. We also indicate a range that if the value is from 60 to 90, a fill will be applied. Look at the screenshot how I did it.

If you have filled it out, do not rush to click on the “OK” button. First you need to click on the “Format” button, as in the screenshot, and go to the fill settings.

Okay, as you can see, the “Format Cell” window has opened. Here you need to go to the “Fill” tab, where you select the one you need, and click on “OK” in this window and in the previous one. I chose a green fill.

Look at your result. I think you succeeded. I definitely succeeded. Take a look at the screenshot:

Let's color a cell a certain color if it is equal to something

Let's return to our table in its original form. And now we will change the color where the number 40 contains to red, and where the number 50 is contained to yellow. Of course, you can use the first method for this matter, but we want to know more Excel capabilities.

This time we will use the Find and Replace function.

Select the section of the table in which we will make changes. If this is the entire sheet, then there is no point in selecting it.

Now it's time to open the search window. On the “Home” tab, in the “Editing” section, click on the “Find and Select” button.

You can also use hotkeys: CTRL + F

In the “Find” field we indicate what we are looking for. In this case, we write “40”, and then click the “Find all” button.

Now that the search results have been shown below, select one of them and press CTRL + A to select them all at once. And then click on “Close” to remove the “Find and Replace” window.

When we have everything containing the number 40 selected, on the “Home” tab in the “Font” section, select the color of the cell. Ours is red. And, as you see on your screen and in my screenshot, they turned red.

Now the same steps need to be followed to color those where the number 50 is indicated. I think now you understand how to do this.

Did you succeed? And look what happened to me.

That's all. Thanks friends. Subscribe, comment, join the group, share on social networks and always be aware of new articles. And also, do not forget to study other articles on this site.