Highlight entire row when cell is active

Question

How to design a sheet script that would result in an active row being highlighted?

I would like to have an entire row change color of font or background when one cell in that row is active.

I don't want the trigger to be any specific value in the cell, just clicking on a cell should trigger the highlight for the whole row that cell belongs to.

Answer 1

Sorry, this can't be done with conditional formatting or script by just selecting a cell. You can, however, highlight an entire row of the active cell with the key combination Shift+Spacebar.

Answer 2

I realize this question was asked a while ago, but I stumbled upon it when I was also looking for this same function. My solution is a little cumbersome and isn't a full solution to what you're looking for, but it combines both a tiny script and a little conditional formatting.

I first wrote a small script using the onEdit() function:

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var targetCell = sheet.getRange('AD1');
  var activeCell = e.range.getA1Notation();
  targetCell.setValue(activeCell);
}

I chose 'AD1' as the target cell, as it was far out of the way and, if need be, I could also choose to hide that column.

Then I went over to the conditional highlighting and typed this in as a custom formula:

=ROW()=ROW(INDIRECT($AD$1))

Voila! Every time I edit a cell, it automatically highlights that entire row.

It's not exactly what you're looking for, as it won't automatically highlight the entire row as soon as you click on a cell ... only when you edit the cell. Also, if you have other formulas running and other conditional formatting going on, your spreadsheet can start to get slow. But this is the closest I've seen out there to a possible solution.

Much less as cool, but still somewhat functional regarding legibility is a basic highlighting of every other row. For example:

in conditional formatting: =ROW()=EVEN(ROW())

Answer 3

The problem you describe can be solved indirectly through the checkbox.

1. Insert column A in the table.
2. In column A, select cells in the rows you want to highlight with color.
3. From the Insert menu, choose Checkbox.
4. Select entire rows in which the check box has been inserted.
5. From the Format menu, choose Conditional Formatting.
6. In the Formatting rules panel, add the Custom formula to this rule.
7. Enter the formula =$A1=TRUE (instead of 1, use the first line number you selected in step 4).
8. Specify the Formatting style.

From now on, after selecting the check box, the entire row will be highlighted.

Answer 4

Sadly this cannot be done via onFocus as we all would prefer, but this works well enough for me using the onEdit event. It's still oddly slow, so perhaps someone could make it faster (certainly from reading / writing to properties, but that's the only way I found to track which row is highlighted).

function onEdit(e){
  manageRowHighlight(e);  
}

function manageRowHighlight(e) {
  var props = PropertiesService.getScriptProperties();
  var prevRow = parseInt(props.getProperty('highlightedRow'));

  var range = e.range;
  var thisRow = range.getRow();

  //if it's same row, just ignore
  if (prevRow == thisRow) {
    return;
  } else if (prevRow != null){
    //else unhighlight it
    range = range.getSheet().getRange(prevRow + ':' + prevRow);
    range.setBackground(null);
  }

  //highlight the current row 
  var range = range.getSheet().getRange(thisRow + ':' + thisRow);
  range.setBackground('#fff2cc')

  //save the row so highlight can be removed later
  props.setProperty('highlightedRow', thisRow);
};

Answer 5

You can use the onSelectionChange event, like this.

In my case, row 1 had some title cells in it with their own background colors. I only highlight the current row if you're in row 2 or later.

function onSelectionChange(e) {
  const range = e.range;
  const sheet = range.getSheet();
  const maxRows = sheet.getMaxRows();
  const maxColumns = sheet.getMaxColumns();

  // Clear the background color from all cells.      
  // (Except row 1 - that has my titles in it)
  sheet.getRange(2, 1, maxRows - 1, maxColumns).setBackground(null);
  
  // Don't set the background color if you're on the first row
  if (range.getRow() > 1) {
    // Highlight the current row
    sheet.getRange(range.getRow(), 1, 1, maxColumns).setBackground("#c9daf8");
  }
}

It takes a second or so to update - I guess that event's a little slow firing.

This works on desktop or touch device.

Answer 6

A workaround that works wonderfully for me was install this app https://www.autocontrol.app/ to redefine the normal behavior of keys into shift+space combination which is actually the shortcut for selecting the current row.

In summary: I changed the behavior of down/up arrows to synthesize a new keyboard input as down-arrow + shift + space. This is the procedure:

• After install the extension create a new rule and use the down arrow as trigger
• Set the behavior to only occurs in Google Sheets by checking if the URL starts-with https://docs.google.com/spreadsheets/
• In the action section select synthesize input (go to advance options>others)
• The specific synthesize is: down arrow, then make the combination shift+space. Do it exactly in this way. The app will show you four buttons.
• In the synthesize box click the space key and set it to action 2 times (this is to select the full row and not only partially in some cases, yet I suggest you to try using "1 times")
• Repeat the process with the UP arrow.

That's all. It took me a time at first but after knowing the trick it is easy. I added an image of how this setting looks like.

Note: Why does the GUI show four buttons in the synthesize box: arrow, shift, space and shift again? This is the expected behavior due to press/release events.

Note2: If the extension in general seems not to be working check the "emergency repair" with right click over the extension icon, or just write the developers.

Answer 7

Reading spreadsheets is difficult when there are many columns. When selecting one cell/row, it will highlight the entire row, otherwise, it won't bother:

function onSelectionChange(e) {
    const sht = SpreadsheetApp.getActive().getActiveSheet();
    const rowCount = sht.getActiveRange().getNumRows();
    const maxRows = sht.getMaxRows();
    const maxColumns = sht.getMaxColumns();

    sht.getRange(2, 1, maxRows - 1, maxColumns).setBackground(null); //skip the first row (headers)

    if (rowCount == 1) {
        const myrow = sht.getActiveRange().getRow();
        if (myrow > 1) { //don't paint the header row
            sht.getRange(myrow, 1, 1, maxColumns).setBackgroundRGB(230,230,130); 
        }
    }
}