Google Spreadsheets conditional formatting based on another cell's content

Question

How can I set conditional formatting on a particular cell that depends on another cell's value?

A | B
1 | x
2 | 
3 | 
1 | x
1 | x
4 | 
8 |

// X can be any value and is here merely to
// mark the cell that should be formatted

In my example I would like to conditionally format column B cells. Those marked with x should be formatted according to value in column A (in the example the value is 1).

Value in B is not supposed to be x I just marked those cells that way here. They may contain any value actually. I just wanted to mark those cells that I want to conditionally format because there's a particular value in sibling column A.

Conditional formatting = cell style formatting (text/background colour) based on some condition.

Answer 1

Complex conditional formatting can be achieved in Google Spreadsheets using Google Apps Script. For example, you could write a function that changes the background colour of an entire row based on the value in one of its cells, something that I do not think is possible with the "Change color with rules" menu. You would probably want to set triggers for this function such as "On Edit", "On Open" and "On Form Submit".

Documentation for setBackgroundRGB() function

UPDATE: Here is a Google Apps Script example of changing the background color of an entire row based on the value in column A. If value is positive, use green. If empty, white. Otherwise, red. See the results in this public Google Spreadsheet. (You will have to be signed in for the script to run, but without signing in you can still see results).

function colorAll() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;
  var endRow = sheet.getLastRow();

  for (var r = startRow; r <= endRow; r++) {
    colorRow(r);
  }
}

function colorRow(r){
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getRange(r, 1, 1, 3);

  var data = dataRange.getValues();
  var row = data[0];

  if(row[0] === ""){
    dataRange.setBackgroundRGB(255, 255, 255);
  }else if(row[0] > 0){
    dataRange.setBackgroundRGB(192, 255, 192);
  }else{
    dataRange.setBackgroundRGB(255, 192, 192);
  }

  SpreadsheetApp.flush(); 
}

function onEdit(event)
{
  var r = event.source.getActiveRange().getRowIndex();
  if (r >= 2) {
    colorRow(r);
  }
}

function onOpen(){
  colorAll();
}
​

​

Answer 2

Edit in response to the question's clarification:

In your Google Spreadsheet window, there is a menu Format with the option "Change color with rules". This is as technical as Google Spreadsheet's conditional formatting is. As far as I can tell, there is no way to color one cell based on another's value - Google doesn't let you enter formulas for other cells.

Unless you're putting other data into column B, you could always make all of column B equal column A, and then use the "Change color with rules" option to color all the cells with the value of 1 with like colors - ie, red background and red text when the value is 1, white background and white text when it is not, effectively hiding the value in column B. That will give you the look you want.

In Excel, you can do what you want. Using your data as an example, I conditionally formatted B1 when this formula is true:

=IF(A1=1,true,false)

and then dragged the formatting down, which highlighted only the cells in B where it's neighbor in column A equaled 1.

---

Original Answer

I'm not sure if you can do this in 2 columns, but I know you can do it in 3:

 A  |  B  |  C
----------------
 1  |  f  |  x

In the B column, you can use the IF formula to see whether an x has been placed in the appropriate C column:

=IF(C1="x",A1,"")

The IF formula has 3 parts - the test, the then value, and the else value. In the above example, the formula checks to see if there is an x in the C column. If there is, it enters the value from A1, otherwise it enters a blank. It doesn't matter if the x is upper- or lower-case.

Once you enter this formula at the top of your list, you can use the drag feature to drag the formula down for the rest of your list. With the cell highlighted in blue, move your mouse over the square in the lower right corner until the cursor becomes a crosshair. Then just click and hold while dragging down to copy the formula to the cells below.

Answer 3

I wrote this web app, Color Code+, with Apps-Script, to cover most of the basic conditional formatting needs. Throw in some rules and it'll spit out code that you can then paste in your spreadsheet, under Tools → Script editor.... (Help thread on Google forums.)