In this article, I’m going to show you how to change the color of a row based on the value of a cell using conditional formatting in Google Sheets. Just follow the steps below and you’ll be able to do it too!
Change the color of a row based on the value of a cell
For the purpose of this demonstration, I’ve created a sample sheet. You can access it here and follow the instructions at your own pace.
Our example showcases a list of foods and the food groups they belong to. We want to change the color of the row for all the foods that belong to the “dairy” food group. To do this, we’ll be using conditional formatting.
1. Select the rows to include
Select the rows you want to include by clicking and dragging over them. If you want to select individual or non-contiguous rows, hold down the Ctrl key (Cmd on Mac) while clicking on the row numbers.
In our example, we want to include all the rows in the conditional formatting, so we’ll select all of them.
2. Click on Format > Conditional formatting
In the toolbar at the top, click on “Format” and then on “Conditional formatting”.
3. Set the format rule to “Custom formula is”
The conditional formatting rules will appear on the right-hand side of the screen. Under the “Format rules” label, locate the dropdown menu that says “Format cells if…” Expand this dropdown menu by selecting it and scroll to the bottom where you’ll see the option “Custom formula is” – Select this option.
4. Enter the custom formula: =$Column and First_Row=Value
Enter the custom formula:
=$Column and First_Row=Value Here’s an explanation of the formula:
-
$Column and First_Row: Here, we need to specify the column the formula will search in, followed by the number of the first row. It’s important to specify the row number to match the first row of your highlighted range of cells. -
Value: We need to specify the value the formula will search for. If the value is a number, we can simply write the number as is. However, if our value is text-based, we need to enclose it in double quotes (” “).
In our example, we want to search for all cells with the word “dairy”. Note that our desired cell value is text-based, so we need to write it as “dairy”. We need to check the column titled “Food Group” which is in Column C and our previously highlighted range starts at Row 3.
The custom formula for our example, using this explanation, would be:
=$C3="dairy"5. Choose the conditional row color
Choose the color the row will take if any of the cells in the selection contain the specific cell value. Select the downward arrow next to the paint bucket icon under the “Formatting style” label in the conditional formatting rules menu.
For this example, we will choose the color orange.
6. Click Done and see the results
Click on the Done button. The conditional formatting will be applied, and the results will be displayed if the criteria are met.
In our example, we can see that “dairy” appears twice in column C. The conditional formatting is applied, and the rows turn orange.
Boost your spreadsheets with Crawlan
Use our insurance reminder software to easily set up custom reminders from your spreadsheet with just a few clicks.
If you enjoyed this article, you might also like our article on how to set up multiple conditional formatting in Google Sheets or our article on how to highlight rows based on the value of a cell in Google Sheets.
If you’re interested in learning how to merge cells in Google Sheets, we recommend checking out our detailed guide.
