You want to apply conditional formatting to a cell. The trouble is… the conditional formatting depends on the value that’s in a different cell. Here’s how you do it.
In this example, we’ve got people’s names in column A. And we’ve got each person’s “score” in the corresponding row in column B. (Here’s the sheet used in our example.)
We’re going to use the person’s score in column B to determine the color of the cell in column A.
In your menu bar, go to Format > Conditional formatting…
Under “Apply to range”, click on the “range selection” icon. Select the cells to which you want to apply conditional formatting. In our example, we’re going to select the cells A2:A1000.
Then click “OK”.
Now comes the fun part. Under Format rules > Format cells if…, select “Custom formula is”. Then enter your formula that evaluates values not in column A but rather column B. Let’s use the following formula as an example.
=B2:B>=80
And there you have it!
P.S. If you wanted, you could also add additional conditional formatting based on custom formulas to:
- set the cell color of A2:A1000 to yellow if the value of B2:B1000 is greater than or equal to 70 and less than 80
- set the cell color of A2:A1000 to red if the value of B2:B1000 is less than 70
=IF(B2:B>=70,IF(B2:B<80,TRUE,FALSE),FALSE)=IF(NOT(ISBLANK(B2:B)),IF(B2:B<70,TRUE,FALSE),FALSE)