Fill cells depending on the value in Microsoft Excel. Untitled Document Exercises and Self-Study
Learn how to quickly change the color of an entire row in Excel sheets based on the value of one cell. Check out tricks and examples of formulas for numeric and text values.
In one of the previous articles, we discussed how to change the color of a cell based on its value. This time we will talk about how to highlight a whole row in Excel 2010 and 2013 depending on the value of one cell, as well as reveal a few tricks and show examples of formulas for working with numeric and text values.
How to change the color of a row based on the numeric value of one of the cells
Suppose we have a table of company orders like this:
We want to color the rows with different colors depending on the ordered quantity of goods (the value in the column Qty.) to highlight the most important orders. Excel tool will help us to cope with this task - “ Conditional formatting».
As you can see, changing the color of an entire row in Excel based on the numeric value of one of the cells is not difficult at all. Next, we will look at a few more examples of formulas and a couple of tricks for solving more complex problems.
How to Create Multiple Priority Conditional Formatting Rules
In the table from the previous example, it would probably be more convenient to use different fill colors to highlight the rows containing in the column Qty. different meanings. For example, create another conditional formatting rule for strings containing a value 10 or more, and highlight them in pink. For this we need the formula:
In order for both rules we created to work at the same time, you need to prioritize them.
How to change the color of a row based on the text value of one of the cells
To simplify the control of order fulfillment, we can highlight in our table with different colors the lines of orders with different delivery status, information about which is contained in the column Delivery:
- If the delivery date of the order is in the future (value Due in X Days), then the filling of such cells should be orange;
- If the order is delivered (value Delivered), then the filling of such cells should be green;
- If the delivery date of the order is in the past (value Past due), then the filling of such cells should be red.
And, of course, the fill color of the cells should change if the order status changes.
With formula for values Delivered and Past due everything is clear, it will be similar to the formula from our first example:
\u003d $ E2 \u003d "Delivered"
\u003d $ E2 \u003d "Past Due"
The task sounds more difficult for orders that must be delivered via X days (value Due in X Days). We can see that the delivery time for various orders is 1, 3, 5 or more days, which means that the above formula does not apply here, as it is aimed at the exact value.
In this case, it is convenient to use the function SEARCH (SEARCH) and to find a partial match, write this formula:
SEARCH ("Due in"; $ E2)\u003e 0
\u003d SEARCH ("Due in", $ E2)\u003e 0
In this formula E2 - this is the address of the cell, based on the value of which we will apply the conditional formatting rule; dollar sign $ needed to apply a formula to an entire line; condition “ >0 ”Means that the formatting rule will be applied if the specified text (in our case it is“ Due in ”) is found.
Prompt: If the formula uses the condition “ >0 “, Then the line will be highlighted in color in each case when the specified text is found in the key cell, regardless of where it is located in the cell. In the example table in the figure below, the column Delivery (Column F) may contain the text “Urgent, Due in 6 Hours” (which in translation means - Urgent, deliver within 6 hours), and this line will also be colored.
In order to highlight the lines in which the content of the key cell begins with the specified text or characters, the formula must be written in this form:
SEARCH ("Due in"; $ E2) \u003d 1
\u003d SEARCH ("Due in", $ E2) \u003d 1
You need to be very careful when using such a formula to check if there are no spaces in the cells of the key column of data. Otherwise, you can rack your brains for a long time, trying to understand why the formula does not work.
So, following the same steps as in, we created three formatting rules, and our table began to look like this:
How to change the color of a cell based on the value of another cell
In fact, this is a special case. Instead of the whole table, select the column or range in which you want to change the color of the cells, and use the formulas described above.
For example, we can set up three of our rules so that only cells containing the order number (column Order number) based on the value of another cell in this row (we use the values \u200b\u200bfrom the column Delivery).
How to set multiple conditions for changing the row color
If you need to highlight lines with the same color when one of several different values \u200b\u200bappears, then instead of creating several formatting rules, you can use the functions AND (AND), OR (OR) and thus combine several conditions in one rule.
For example, we can mark orders expected within 1 and 3 days in pink, and those that will be completed within 5 and 7 days in yellow. The formulas will look like this:
OR ($ F2 \u003d "Due in 1 Days"; $ F2 \u003d "Due in 3 Days")
\u003d OR ($ F2 \u003d "Due in 1 Days", $ F2 \u003d "Due in 3 Days")
OR ($ F2 \u003d "Due in 5 Days"; $ F2 \u003d "Due in 7 Days")
\u003d OR ($ F2 \u003d "Due in 5 Days", $ F2 \u003d "Due in 7 Days")
In order to select orders with the quantity of goods at least 5, but not more than 10 (value in the column Qty.), write the formula with the function AND (AND):
AND ($ D2\u003e \u003d 5; $ D2<=10)
\u003d AND ($ D2\u003e \u003d 5, $ D2<=10)
Of course, in your formulas you can use not necessarily two, but as many conditions as required. For instance:
OR ($ F2 \u003d "Due in 1 Days"; $ F2 \u003d "Due in 3 Days"; $ F2 \u003d "Due in 5 Days")
\u003d OR ($ F2 \u003d "Due in 1 Days", $ F2 \u003d "Due in 3 Days", $ F2 \u003d "Due in 5 Days")
Prompt: Now that you have learned how to color cells in different colors, depending on the values \u200b\u200bthey contain, you may want to find out how many cells are highlighted in a certain color and calculate the sum of the values \u200b\u200bin these cells. I want to please you, this action can also be done automatically, and we will show the solution to this problem in the article devoted to the question How to calculate the quantity, amount in Excel and set up a filter for cells of a certain color.
We have shown just a few of the possible ways to make a table look like a striped zebra, the color of which depends on the values \u200b\u200bin the cells and can change along with the change in these values. If you are looking for something different for your data, let us know, and together we will definitely come up with something.
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.
Range Object.Interior.Color
Starting in Excel 2007, the primary way to fill a range or an individual cell with color (sketch, add, change the background) is to use the .Interior.Color property of the Range object by assigning 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 \u003d 31569 Range ("A4: D8"). Interior.Color \u003d 4569325 Range ("C12: D17"). Cells (4). Interior.Color \u003d 568569 Cells (3, 6) .Interior.Color \u003d 12659 End Sub
Place the sample code in your code module and click the button on the Run Sub toolbar or on the F5 keyboard, the cursor should be inside the running program. On the active Excel sheet, the cells and the range selected in the code will be colored in the appropriate colors.
There is one interesting nuance: if you assign to the property .Interior.Color negative value from -16777215 to -1, then the color will correspond to the value equal to the sum of the maximum palette value (16777215) and the assigned negative value. For example, the filling of all three cells will be the same after executing the following code:
Sub ColorTest11 () Cells (1, 1) .Interior.Color \u003d -12207890 Cells (2, 1) .Interior.Color \u003d 16777215 + (-12207890) Cells (3, 1) .Interior.Color \u003d 4569325 End Sub
Checked in Excel 2016.
Displaying messages about numerical color values
It is impossible to remember the numerical values \u200b\u200bof colors, so the question often arises of how to find out the numerical value of the cell background. The following VBA Excel code displays messages about the numerical values \u200b\u200bof 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 \u200b\u200bto variables by declaring them as Long.
Using predefined constants
VBA Excel has predefined constants for commonly used colors for filling cells:
Assigning a color to a cell by a predefined constant in VBA Excel is the same as a numeric value:
Code example 3:
Range ("A1"). Interior.Color \u003d vbGreen
RGB color model
The RGB color system is a combination of three different primary colors: red, green, and blue. They can take values \u200b\u200bfrom 0 to 255. If all values \u200b\u200bare equal to 0 - this is black, if all values \u200b\u200bare equal to 255 - this is white.
You can select a color and find out its RGB values \u200b\u200busing the Excel palette:
Before you can assign a color to a cell or range using RGB values, it must be converted to a decimal number representing the color. For this, there is a VBA Excel function, which is called RGB.
Code example 4:
Range ("A1"). Interior.Color \u003d RGB (100, 150, 200)
Range Object.Interior.ColorIndex
Prior to 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:
Example code 5:
Range ("A1"). Interior.ColorIndex \u003d 8 MsgBox Range ("A1"). Interior.ColorIndex
You can view the limited palette for filling cells with a background by running a simple macro in VBA Excel:
Code example 6:
Sub ColorIndex () Dim i As Byte For i \u003d 1 To 56 Cells (i, 1) .Interior.ColorIndex \u003d 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 the background corresponding to the index.
You can see the finished standard palette of 56 colors.
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 select several Excel cells in different colors under a certain condition. Would you like to know how this is done? In this tutorial, we will make the cell color change depending on the Excel value, and also color all the cells using search.
Fill color changes with value
For example, we will practice making a cell change color in a given table under a certain condition. Yes, none, but all with a value in the range from 60 to 90. For this we will use the "Conditional Formatting" function.
First, select the range of data that we will format.
Next, we find the button "Conditional Formatting" on the "Home" tab and select "Create Rule" from the list. 
We have opened the "Create formatting rules" window. In this window, select the type of rule: "Format only cells that contain".
Next, go to the section "Change the description of the rule", where you need to specify the conditions according to which the filling will be performed. In this section, you can set a variety of conditions under which it will change.
In our case, you need to put the following: "cell values" and "between". We also designate the range that a fill will be applied if the value is from 60 to 90. Look at the screenshot how I did it. 
Of course, when working with your spreadsheet, you may need to fill in completely different conditions, which you will indicate, well, now we are just training.
If you have filled in, then 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.
Ok, as you can see, you have opened the "Format Cell" window. Here you need to go to the "Fill" tab, where you select the desired one, and click on "OK" in this window and in the previous one. I chose a green fill. 
Look at your result. I think you did it. I definitely did it. Take a look at the screenshot: 
Let's paint the cell in a certain color if it is equal to something
Let's go back to our table in its original form. And now we will change the color where the number 40 is contained to red, and with the number 50 to yellow. Of course, for this case, you can use the first method, but we want to know more Excel features.
This time we will use the Find and Replace function.
Select the part of the table to which we will make changes. If this is the whole sheet, then there is no point in highlighting.
Now is the time to open the search box. On the Home tab in the Editing section, click 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, 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 hide the "Find and Replace" window. 
When we have selected all containing the number 40, on the "Home" tab in the "Font" section, select the color of the cell. We have it red. And, as you can see on your screen, and on my screenshot, they turned red. 
Now you need to perform the same steps to color those where the number 50 is indicated. I think now you understand how to do this.
Did you succeed? Look what happened to me. 
That's all. Thanks friends. Subscribe, comment, join the group, share on social networks and be always aware of new articles. Also, do not forget to study other articles on this site.
If the value in a cell satisfies a user-defined condition, then using can select this 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 the range A6: C16 there is a table with a list of works, deadlines and their completion status (see the example file).
Task1 - text values
It is necessary to highlight in color 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 plate with the statuses of works in the range E6: E9 .
Select a range of cells A7: C17 containing a list of works, and set through the menu Home / Fill color the background of the fill is red (we assume that all works are initially in the status Not started).
Make sure a range of cells is selected A7: C17 (A7 must be ). Let's call the menu command Conditional Formatting / Create Rule / Use Formula to Define Formatted Cells.
- in field " Format values \u200b\u200bfor which the following formula is true»You need to enter \u003d $ C7 \u003d $ E $ 8 (in the cell E8 is the value In work). Pay attention to the use;
- push the button Format;
- select tab Fill;
- choose gray ;
- Click OK.
ATTENTION: Once again I draw your attention to the formula \u003d $ C7 \u003d $ E $ 8. Typically, users enter \u003d $ C $ 7 \u003d $ E $ 8, i.e. enter an extra dollar symbol.
You need to do the same to highlight works in the status Completed... The formula in this case will look like \u003d $ C7 \u003d $ E $ 9, and set the fill color to green.
As a result, our table will look like this.
To quickly expand Conditional Formatting rules to a new row in a table, select the new row cells ( A17: C17 ) and press . The rules will be copied to the string 17 tables.
Task2 - Dates
Suppose that a journal is kept of how employees attend scientific conferences (see. example file date sheet).
Unfortunately, the Date Visited 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 no.
Column D created \u003d MAX (($ A7 \u003d $ A $ 7: $ A $ 16) * $ B $ 7: $ B $ 16) \u003d $ B7which 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 much simpler \u003d $ B7 \u003d 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 suppose that the column with dates has been sorted and you want to select rows whose dates of visit fall within a certain range.
To do this, use the formula \u003d AND ($ B23\u003e $ 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 must 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 $ before B), but the reference to the row should change depending on the table row (otherwise all date values \u200b\u200bwill be compared with the date from B23 ).
Thus, the UV rule for example for a cell A27 will look \u003d AND ($ B27\u003e $ E $ 22; $ B27<$E$23) , т.е. A27 will be highlighted because in this line the date from B27 falls into 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 \u003d AND ($ B31\u003e $ E $ 22; $ B31<$E$23) , т.е. В31 не будет выделена, т.к. в этой строке дата из B31 does not fall within the specified range.
