Excel Tining Cell at a value. As in Excel, select the cells by color by condition. Changing the fill in manual

In order for in the document made in MS Excel, to proper emphasis, it is possible to increase the sizes of cells, select another font or size of letters, and you can paint the squares to which you definitely need to pay attention.

Now we'll figure it out how to allocate cells with color in Excele, or change the color of those that are already painted. How to make the cell change the color on a given condition, depending on the values \u200b\u200bincluded in it, and how to work with the rules created.

Simple bloc fill

To paint one or more blocks in Excel is not difficult. First, select them and on the Home tab, click on the arrow near the bucket with paint to expand the list. Select the appropriate color from there, and if nothing is suitable, press "other colors".

By the way, in this way, you can pour a whole string, just click on its number to allocate. About the selection of cells in Excele can read a separate article.

If you work with a table, in which something has already been painted, then change the color of the blocks, or it can be removed at all. Click on it from colors or select New, or click on the "No" option.

Depending on the data entered

Now consider how to make the cell change color based on the specified condition. It is used for this conditional formatting, about which there is a separate article on the site.

Textic

Take the following table for example. Let's make it so that the red apple is apple, yellow - pear, and orange - orange.

We allocate the data with which we will work in my case are the names of fruit. Then click "Conditional formatting"which will be constantly used further. From the list click on "Create Rule".

Opens this window. At the top choose type - "Format only cells that contain", Further, too, we will celebrate it. Low below, we specify the conditions: we have text that contains certain words. In the last field or click on the button and specify the cell, or enter the text.

The difference is that putting a reference to the cell (\u003d $ b $ 4), the condition will change depending on what is typed. For example, instead of an apple in B4 I will specify the currant, accordingly the rule will change, and blocks with the same text will be painted. And if it is in the field to enter an apple, then this word will be sought specifically, and it will not depend on anything.

Here, select the color of the fill and click "OK". To view all options, click on the "Other" button.

The rule is created and saved it by pressing the "OK" button.

As a result, all the blocks in which the specified text was painted in red.

Also create rules for the rest of the fruit, only another casting option is selected.

Numeric

Now we'll figure it out with numeric data. Let's post the number in the column D to be placed on a specific background by the condition that set.

We highlight the column, create a rule, indicate its type. Further we prescribe - the "value" "more" "15". The last number can or enter manually, or specify the cell address from where the data will be taken from. We are determined with the fill, click "OK".

Blocks where numbers are entered more selected, painted.

Let's specify more for allocated cells yet - select "Rules Management".

Here everyone choose, as I described above, only you need to change the color and put the condition "less or equal".

When everything is ready, click "Apply" and "OK".

Everything works, equal values \u200b\u200band below 15 are painted pale blue.

There would be a good difference between the brought and sold goods sold here. Select two columns and click "Rules for allocation of cells" – "Repeating values". Choose suitable color. After that, if two cells nearby are colored - it means that the fruits that were delivered, all sold.

Let's show on the example of the last column, how to make the cell change color depending on the specified value. We allocate them and go to "Rules Management".

Create a new one for the current fragment, if you need to choose in this drop-down list, or for the entire sheet of whole, or for other sheets.

Choose the desired items in the window that opens. I snap dark green all the meanings that more than 90. Since in the last field, I specified the address (\u003d $ F $ 15), then when the number 90 changes in the cell, for example, by 110, the rule will also be changed. Save the changes by clicking on the "OK" button.

We will create another rule, but it is highlighted light green all that is less than or equal to 90. Do not forget to save everything by clicking on the buttons on the right below.

That's what happened. From the last column, you can visually quickly determine profits from sales of which product was more.

For example, if the price changes, the rules will be revised. If the value becomes more or less than the specified, then the cell color will automatically change.

I think you noticed that the rule is created not only for text and numbers, but also dates, and depending on the filling and availability of errors in the cells. Specify the condition, choose the color and save.

To see what you have submitted, select Range and window "Rules Management" There will be a complete list. Using the buttons at the top you can add, change or delete.

To quickly find and select all cells containing certain types of data, such as formulas using the command Go. In addition, with Go to Find only cells that correspond to certain criteria - for example, the last cell on a sheet containing data or formatting.

Make the following:

Click	To highlight
Notes	Cells with notes.
Constants
formulas	Note: Flags under the parameter formulas Determine the type of formulas.
Empty	Empty cells.
Current area	current area, such as the entire list.
Current array	The whole array, if the active cell is contained in the array.
Objects	Graphic objects (including charts and buttons) on the sheet and in text fields.
Differences on strings	All cells that differ from the active cell in the selected string. In the selection mode there is always one active cell, whether the range, strings or column is. Using the Enter or Tab key, you can change the location of the active cell, which by default is the first cell in the string. If more than one line is selected, the comparison is performed for each individual series of the dedicated fragment and the cell that is used to compare for each additional line in the same column as an active cell.
Differences in columns	All cells that differ from the active cell in the highlighted column. There is always one active cell in the selected fragment whether it will be a range, string or column. Using the Enter or Tab key, you can change the location on the active cell - the default is the first cell in the column. If you choose more than one column, the comparison is performed for each individual column of the selected fragment. Cells that are used for comparison for each additional column is located in the same line in the active cell.
Influencing cells	Cells referenced by the formula in the active cell. In chapter dependent cells only directlyto find only those cells for which formulas refer directly; at all levelsTo find all cells to which cells in the dedicated range refer directly or indirectly.
Dependent cells	Cells with formulas that refer to the active cell. Perform one of the following actions. Select option only directlyTo find only cells with formulas referred directly to the active cell. Select option at all levelsTo find all the cells that refer to the active cell directly or indirectly.
Last cell	Last cell on a sheet containing data or formatting.
Only visible cells	Only cells visible in the range that intersects with hidden rows and columns.
Conditional formats	Only cells that are applied conditional formatting. In chapter "Data checking" Perform one of the following actions. everythingto find all the cells to which conditional formatting is applied; these sameTo find cells with the same conditional formatting as in the dedicated cell.
Data checking	Only cells that are applied data verification rules. Perform one of the following actions. Select option everythingTo find all the cells for which data is enabled. Select option these sameTo find cells to which the same data verification rules are applied as the selected cell.

additional information

You can always ask a question by Excel Tech Community, ask for help in the Answers Community community, as well as offer a new feature or improvement on the website.

Hello, dear readers. Have you ever been able to work with huge data in the table? You know, it will work with them much more convenient if you know how to select multiple cells Excel in various colors under a certain condition. Would you like to know how this is done? In this lesson we will do so that the color of the cells changed depending on the Excel value, as well as color all cells by searching.

The color of the fill is changing along with the value

For example, we draw on the cell to change the color in this table under a specific condition. Yes No, but all with a value in the range from 60 to 90. To do this, we will use the "Conditional Formatting" function.

To begin with, select the data range that we will format.

Next, we find on the "Home" tab, the "Conditional Formatting" button and select "Create Rule" in the list.

We have opened the "Creating Formatting Rules" window. In this window, select the rule type: "format only cells that contain".

Next, go to the "Change the rule description" section, where you need to specify the conditions for which the fill will be performed. In this section, you can set the most different conditions under which it will change.

In our case, it is necessary to put the following: "Cell values" and "between". We also refer to the range that, subject to the value from 60 to 90, the fill will be applied. Look at the screenshot, as I did.

Of course, when working with your table, you may need to fill out by completely different conditions that you will indicate, well, and now we just train.

If you are filled, then do not rush to click on the "OK" button. Before you must click on the "Format" button, as in the screenshot, and go to the fill setting.

Well, as you can see, you have opened the "Cell Format" window. Here you need to go to the "Fill" tab, where you choose the desired, and click on "OK" in this window and in the previous one. I chose a green fill.

Look at your result. I think you all happened. I exactly happened. Take a look at the screenshot:

Paint the cell in a certain color if it is equal to something

Let's go back to our table in original form. And now we will change the color where there is a digit 40 on the red color, and with a number 50 on yellow. Of course, for this case, you can take advantage of the first way, but we want to know more Excel features.

This time we will use the "Find and Replace" function.

Highlight that part of the table in which we will make changes. If this is the whole sheet, it makes no sense to allocate.

Now the time to open the search box. On the Home tab, in the Edit section, click on the "Find and Allocate" button.

You can also use the hot keys: 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, when the search results were shown below, select one of them and click on the Ctrl + A combination to select them all at once. And then click on "Close" to remove the "Find and Replace" window.

When we selected all containing the number 40, on the Home tab, in the "Font" section, select Cell color. We have it red. And as you can see on the screen, and in my screenshot, they painted in red.

Now the same actions need to be performed to paint those where the number 50 is indicated. I think now you understand how to do it.

Did you get? And see what happened to me.

That's all. Thanks friends. Subscribe, Comment, join the group, share in social networks and always up to date with new articles. And also, do not forget to learn other articles on this site.

In order for in the document made in MS Excel, to proper emphasis, it is possible to increase the sizes of cells, select another font or size of letters, and you can paint the squares to which you definitely need to pay attention.

Now we'll figure it out how to allocate cells with color in Excele, or change the color of those that are already painted. How to make the cell change the color on a given condition, depending on the values \u200b\u200bincluded in it, and how to work with the rules created.

Simple bloc fill

To paint one or more blocks in Excel is not difficult. First, select them and on the Home tab, click on the arrow near the bucket with paint to expand the list. Select the appropriate color from there, and if nothing is suitable, press "other colors".

By the way, in this way, you can pour a whole string, just click on its number to allocate. About the selection of cells in Excele can read a separate article.

If you work with a table, in which something has already been painted, then change the color of the blocks, or it can be removed at all. Click on it from colors or select New, or click on the "No" option.

Depending on the data entered

Now consider how to make the cell change color based on the specified condition. It is used for this conditional formatting, about which there is a separate article on the site.

Textic

Take the following table for example. Let's make it so that the red apple is apple, yellow - pear, and orange - orange.

We allocate the data with which we will work in my case are the names of fruit. Then click "Conditional formatting"which will be constantly used further. From the list click on "Create Rule".

Opens this window. At the top choose type - "Format only cells that contain", Further, too, we will celebrate it. Low below, we specify the conditions: we have text that contains certain words. In the last field or click on the button and specify the cell, or enter the text.

The difference is that putting a reference to the cell (\u003d $ b $ 4), the condition will change depending on what is typed. For example, instead of an apple in B4 I will specify the currant, accordingly the rule will change, and blocks with the same text will be painted. And if it is in the field to enter an apple, then this word will be sought specifically, and it will not depend on anything.

Here, select the color of the fill and click "OK". To view all options, click on the "Other" button.

The rule is created and saved it by pressing the "OK" button.

As a result, all the blocks in which the specified text was painted in red.

Also create rules for the rest of the fruit, only another casting option is selected.

Numeric

Now we'll figure it out with numeric data. Let's post the number in the column D to be placed on a specific background by the condition that set.

We highlight the column, create a rule, indicate its type. Further we prescribe - the "value" "more" "15". The last number can or enter manually, or specify the cell address from where the data will be taken from. We are determined with the fill, click "OK".

Blocks where numbers are entered more selected, painted.

Let's specify more for allocated cells yet - select "Rules Management".

Here everyone choose, as I described above, only you need to change the color and put the condition "less or equal".

When everything is ready, click "Apply" and "OK".

Everything works, equal values \u200b\u200band below 15 are painted pale blue.

There would be a good difference between the brought and sold goods sold here. Select two columns and click "Rules for allocation of cells" – "Repeating values". Choose suitable color. After that, if two cells nearby are colored - it means that the fruits that were delivered, all sold.

Let's show on the example of the last column, how to make the cell change color depending on the specified value. We allocate them and go to "Rules Management".

Create a new one for the current fragment, if you need to choose in this drop-down list, or for the entire sheet of whole, or for other sheets.

Choose the desired items in the window that opens. I snap dark green all the meanings that more than 90. Since in the last field, I specified the address (\u003d $ F $ 15), then when the number 90 changes in the cell, for example, by 110, the rule will also be changed. Save the changes by clicking on the "OK" button.

We will create another rule, but it is highlighted light green all that is less than or equal to 90. Do not forget to save everything by clicking on the buttons on the right below.

That's what happened. From the last column, you can visually quickly determine profits from sales of which product was more.

For example, if the price changes, the rules will be revised. If the value becomes more or less than the specified, then the cell color will automatically change.

I think you noticed that the rule is created not only for text and numbers, but also dates, and depending on the filling and availability of errors in the cells. Specify the condition, choose the color and save.

To see what you have submitted, select Range and window "Rules Management" There will be a complete list. Using the buttons at the top you can add, change or delete.

Function \u003d Coloring (Cell) Returns the color code of the selected cell. It has one mandatory argument:

• CELL - Link to the cell for which you want to apply the function.

Below is an example demonstrating function.

Attention should be paid to the fact that the function is not translated automatically. This is due to the fact that the change in the color of the Filling of the Excel cell does not lead to the recalculation of formulas. To recalculate the formula, you must use the key combination Ctrl + Alt + F9

Example of use

Since the fill of cells greatly simplifies the perception of data, almost all users love to use it. However, there is a big minus - in the standard Excel functionalitythere is no ability to perform operation based on the color of the fill. It is impossible to sum up the cells of a certain color, calculate their number, find the maximum and so on.

Using a function Coloring All this becomes fulfilled. For example, "stretch" this formula with the color of the fill in the adjacent column and perform calculations based on the numeric cell code.

Code on VBA.

Public Function Flower (AS Range) AS Double Flashing \u003d Cell.inter.color End Function