1

In Google Sheets I'm trying to find a way to conditionally format the font size of the cell for Column A in a row, based on text found within the cell of Column B in the same row.

For example, if Column B in Row 2 contains "Most Wanted", then Column A in Row 2 should be font size 12, bold and underlined. (Normally it is plain text and size 10.)

It's pretty easy to change bold and underline formatting with the built-in conditional formatting but I can't find a way to change the font size as well.

Right now I'm using an adapted script I found and edited it to suit my needs. But it changes the font for the entire row. I'm not sure if there's something simple I need to edit or if I need an entirely new script. I'm not great with scripts. I was only able to edit the easy stuff. Below is the adapted script I'm using now and here is an editable test sheet that it's currently attached to: https://docs.google.com/spreadsheets/d/1N4BT50HifwBS_qP4Tt6kxqwEOp2N-wVQxv7Jo2t0vQA/edit?usp=sharing

Any help would be greatly appreciated.

//Sets the row format depending on the value in the "Wanted Level" column.
function setRowColors() {
  var range = SpreadsheetApp.getActiveSheet().getDataRange();
  var wantedlevelColumnOffset = getWantedlevelColumnOffset();

  for (var i = range.getRow(); i < range.getLastRow(); i++) {
    rowRange = range.offset(i, 0, 1);
    wantedlevel = rowRange.offset(0, wantedlevelColumnOffset).getValue();
    if (wantedlevel == 'Most Wanted') {
      rowRange.setFontSize('12').setFontWeight('bold').setFontLine('underline').clear;
    } else if (wantedlevel == 'Want') {
      rowRange.setFontSize('10').setFontWeight('bold').setFontLine('none');
    } else if (wantedlevel != 'Most Wanted' || 'Want') {
      rowRange.setFontSize('10').setFontWeight('normal').setFontLine('none');
    }
  }
}

//Returns the offset value of the column titled "Wanted Level"
//(eg, if the 7th column is labeled "Wanted Level", this function returns 6)
function getWantedlevelColumnOffset() {
  lastColumn = SpreadsheetApp.getActiveSheet().getLastColumn();
  var range = SpreadsheetApp.getActiveSheet().getRange(1,1,1,lastColumn);

  for (var i = 0; i < range.getLastColumn(); i++) {
    if (range.offset(0, i, 1, 1).getValue() == "Wanted Level") {
      return i;
    }
  }
}
  • Not the font -size- you can't. At least not that I can find. – Computer Candy Oct 27 '16 at 5:57
  • That's true. I'm sorry. – Rubén Oct 27 '16 at 5:58
  • I hope they add it soon, though. :) – Computer Candy Oct 27 '16 at 6:01
0

Instead of range.offset(...) use something like SpreadsheetApp.getActiveSheet().getRange(i,1)

  • That did the trick when i replaced rowRange = range.offset(i, 0, 1); with rowRange = SpreadsheetApp.getActiveSheet().getRange(i,1). Thank you! – Computer Candy Oct 27 '16 at 6:15
  • A quick follow up question. Ever since I added the script, the undo arrows and Ctrl Z don't work. Is it possible that the script broke the undo feature somehow? – Computer Candy Oct 27 '16 at 6:17
  • Oh, it's messing with Column A in my header rows. How can i exclude them from effect? Thank you again! :) – Computer Candy Oct 27 '16 at 6:20
  • Regarding the second comment, scripts should not break Ctrl Z. Regarding the third comment, check the start value of i, the first row is 1, so if you want to skip the header rows, you should use 2 instead. – Rubén Oct 27 '16 at 12:04
  • Thank you! I'm not sure what is going on with the undo feature. It works on all my other sheets but will not on this one. In fact, when I delete the script, it works fine again. Give it a try in the test sheet I have linked in my original question. – Computer Candy Oct 27 '16 at 15:47

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.