In this article we will show you how to highlight a row based on another cell value with conditional formatting in Google Sheets. Simply follow the steps below.
A sample sheet has been created for the purpose of this example, access this and follow along by clicking here.
In this example we will have a list of 9 people and the cities they live in. We want to highlight any rows of the people who live in New York. To do this we can use conditional formatting with a custom formula.
Select the range of rows by clicking and dragging over the rows to select them.
If you need to select rows that are separate from each other hold the Ctrl (Cmd ⌘ on Mac) key whilst clicking the individual rows.
In our example we want to apply the condition to the entire dataset so will select all rows that contain data.
From the file menu select click Format, a menu will display. Select Conditional formatting.
The conditional formatting rules menu will now appear on the right hand side of the display.
In the section labelled Format rules expand the Format cells if.. dropdown menu. At the bottom of this menu select Custom formula is.
An input box will appear, enter the formula:
=Cell_Ref=”Value”
Formula Breakdown:
$Cell_Ref: This is the column you want the formula to search followed by the first row. It is important the row number matches the first row of your selection. The dollar sign ($) needs to prefix the cell reference to apply the search to the entire column.
=Value: This is the value you want the formula to check against. Any text values must be contained in double quotations (“ ”), numerical values do not need any quotations.
In our example we need to set our custom formula to search for the text value “New York” located in column D. Our selection starts from row number 4 therefore our complete formula will be:
=$D4=”New York”
In the Formatting style section you can now set various format options including the ability to bold, italic, underline and strikethrough text. You can also change the text and cell background highlight.
In our example we will bold the text and turn the cell highlight purple. This will apply the highlight to the entire row.
Click the Done button which will apply the custom formula and any row that meets the conditions set in the formula will highlight purple.
As can be seen in our example all the rows that contain the text value “New York” in column D are highlighted purple.
If you enjoyed this article, you might also like our article on how to change color rows based on cell value in Google Sheets. To learn how to use the Google Sheets script to automatically send emails, we recommend checking out our tutorial.
