In this article we will show you how to apply conditional formatting based on another cell color in google sheets. Simply follow the steps below.
For the purpose of this demonstration a sample sheet was created. Click here to access the sheet and follow along.
While Google Sheets does not currently offer a method to conditionally format a cell based on another cell's color we can use a workaround to achieve the same result. You will need to use alternative cell conditions to format the other.
Here's an example of how you can do this:
Click and drag across the cells to be included, this will highlight them.
In our example we will highlight all of the cells under the Name column.
From the file menu at the top of the screen click on Format, from the dropdown menu select Conditional formatting.
The Conditional format rules menu will now appear on the right hand side of the screen.
In the Format rules section you will see the “Format cells if” drop down menu, expand this and scroll to the bottom. Click on “Custom formula is”.
As there is no formula to choose a condition based on color we must choose a different condition.
In the input box, enter the formula:
```=$Cell_ref[operator]value```
Formula Breakdown:
$Cell_ref: This is the column the formula will search followed by the first row number of your selection. It is important to ensure the first row number matches the first row in your selected cell range.
[operator]: This will be dependent on what condition you wish to use. In our example we will use the equals operator to check if the value is present or not.
Value: This will be the value the formula is searching against. Text values need to be contained in double quotation marks (“ ”) whereas numeric values do not.
In our example as we cannot use a formula based on the cell color we will base it on the text value instead.
Our example formula is:
```=$E3="PASS"```
Under the Formatting style section choose to format your cells, there are options to make the text bold, italic, underlined, stricken through or change the text color. There is also the option to change the cell color.
In our example we will apply the cell color to match the color of the PASS cell which is Green.
Select the done button and the formatting will be applied to the specified conditions.
As can be seen in our example the cells in the name column that match the cells stating “PASS” have turned green.
Use our software for invoice reminders to easily set up custom reminders from your spreadsheet in just a few clicks.
If you enjoyed this article, you might also like our article on how to use conditional formatting to highlight rows in Google Sheets or our article on how to use Google Sheets conditional formatting based on another column.
If you want to learn how to send emails based on cell value in Google Sheets, we also suggest checking out our detailed guide.
