.png)
Conditional Formatting Based on Cell Color in Google Sheets
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.
Apply Conditional Formatting Based on Another Cell Color
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:
1. Select the range of cells that you want to apply formatting to.
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.
2. In the file menu, click "Format" followed by "Conditional formatting"
From the file menu at the top of the screen click on Format, from the dropdown menu select Conditional formatting.
3. In the "Format cells if" dropdown menu, select "Custom formula is."
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”.
4. Enter the formula: =$Cell_ref[operator]value
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"```
5. Click the "Format" button to choose the formatting that you want to apply.
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.
6. Click “Done” to see the results
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.
