In this article we will show you how to change row color based on a cell value with conditional formatting in google sheets. Simply follow the steps below.
For the purposes of this demonstration we have created a sample sheet. Click here to access this sheet and follow along at home.
Our example shows a series of foods and the food groups they belong to. We want to change the row color of all foods that belong to the dairy food group, to do this - we need to use conditional formatting.
Highlight the rows you want to include by clicking and dragging over the row numbers.
If you want to select individual or separate rows press and hold the Ctrl (Cmd on Mac) key as you individually click the row numbers.
In our example we want all the rows to be included in the conditional format so will highlight them all.
From the toolbar at the top click on Format followed by Conditional formatting.
The conditional formatting rules will appear on the right hand side of the display.
Underneath the Format rules label note the dropdown menu labelled Format cells if ..
Expand this drop down menu by selecting it and scroll to the bottom where you will see an option of Custom formula is - Select this option.
Enter the custom formula:
=$Column and First_Row=Value
Formula Breakdown:
$Column and First_Row: Here we need to specify the column the formula is going to search in followed by the first row number. It is important to specify the row number to match the first row in your highlighted cell range.
Value: We need to specify this as the value the formula will look for. If the value is a number we can simply write the number as standard, however if our value is a text based value we need to enclose it in double quotations (“ ”).
In our example we want to look 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 labelled Food Group which is in Column C and our previously highlighted range starts at Row 3.
The custom formula for our example using this breakdown is:
=$C3=”Dairy”
Choose the color the row will become if any of the cells in the selection contain the specific cell value.
Select the downward arrow next to the paint bucket icon underneath the Formatting style label in the conditional format rules menu.
For this example we will choose the color orange.
Click the Done button. The conditional formatting will be applied and results will display if the criteria is met.
In our example we can see “Dairy” appears twice in Column C. The conditional formatting is applied and the rows turn orange.
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 cell value in Google Sheets.
If you want to learn how to mail merge labels in Google Sheets, we also suggest checking out our detailed guide.
