Looking for a "crosshair" highlight function in Google Sheets

Question

I'm having a problem in Google Sheets tracking rows and columns to which a cell belongs in a large spreadsheet.

What would be really helpful and simple would be a "crosshair" function that, when active, automatically highlights the row AND column of the currently selected cell, so that when I select a cell, it looks so:

This would make the job of tracking cells on larger spreadsheets SO much easier. Ideally it could be set to highlight row only, column only, or both, but for my needs, I'm looking to highlight both automatically.

Is this at all possible? Is there a script to do this?

score 2 · Accepted Answer · answered Sep 12 '14 at 19:41

2

CTRL + SPACE highlights the column
SHIFT + SPACE highlights the row

They don't work in conjunction unfortunately.

If I have time this weekend, I'll write an add-on.

answered Sep 12 '14 at 19:41

caleb.breckon

1,336
18
42

1

Hi, I thought I would bump as the need for this has come up again. Thanks for any help you can provide. – Pablo Carson Nov 17 '14 at 17:20
1

Bumping again to ask if anyone can help with this. Thanks in advance. – Pablo Carson Apr 15 '15 at 17:44
those don't *highlight*, they **select** – Christoph Rackwitz Jul 16 '23 at 23:04

score 0 · Answer 2 · answered Apr 30 '20 at 06:14

0

Well I found a way for selecting the entire row automatically. I just installed a shortcut manager to modify the keys behavior. I explained it in fully detail here: https://stackoverflow.com/a/61516763/9356315

I think you can easily extend it to select the entire column at the same time. I hope this will help.

answered Apr 30 '20 at 06:14

Jason Angel

2,233
1
14
14

*selection* of an entire row/column is built into Google Sheets. the hotkeys are ctrl+space and shift+space – Christoph Rackwitz Jul 16 '23 at 23:05

score 0 · Answer 3 · answered May 07 '20 at 13:03

Actually working around the same issue. I've just created a script and then calling it with a macro which allows me to configure a shortcut.

function highlightCoords() {
  var ss = SpreadsheetApp.getActive();
  var s = ss.getActiveSheet();
  var a = ss.getActiveCell();

  var col = a.getColumn();
  var row = a.getRow(); 

  var r = s.getRange(row, 1, 1, col-1);
  var c = s.getRange(1, col, row-1, 1);
  var ar = s.getRange(row, col);      

  ss.getRangeList([r.getA1Notation(), c.getA1Notation(), ar.getA1Notation()]).activate();
};

Then my macro will trigger that functions.

For easy call, shortcut will be associated. Simple like: [Ctrl + Alt + Shift + 1]

Notice the active cell will be selected making F2 available for edition

score 0 · Answer 4 · answered Feb 24 '21 at 09:50

0

You can do it with a script. This article explain in details on how to highlight row, column and crosshair: https://jec.fyi/blog/highlight-apps-script

answered Feb 24 '21 at 09:50

Jecfish

4,026
1
18
16

score 0 · Answer 5 · answered Jul 29 '21 at 09:33

var userProperties = PropertiesService.getUserProperties();
var H_MODE = 'M';

function getMode() {
  return userProperties.getProperty(H_MODE);
}

function setMode(m) {
  userProperties.setProperty(H_MODE,m);
}

function createHighlightMenu() {
  setMode('off');
  SpreadsheetApp
    .getActiveSpreadsheet()
    .addMenu('Highlight', generateMenu());
}

function updateHighlightMenu() {
  SpreadsheetApp
    .getActiveSpreadsheet()
    .updateMenu('Highlight', generateMenu());
}

function generateMenu() {
  return [
    {name: (getMode()=='off'? " ✓ " :'')+'Off', functionName: 'setOff'},
    {name: (getMode()=='row'? " ✓ " :'')+' Highlight row', functionName: 'setHighlightRow'},
    {name: (getMode()=='column'? " ✓ " :'')+' Highlight column', functionName: 'setHighlightCol'},
    {name: (getMode()=='crosshair'? " ✓ " :'')+' Highlight crosshair', functionName: 'setHighlightCrosshair'}
  ];
}

function setOff() {
  setMode('off');
  updateHighlightMenu();
}

function setHighlightRow(){
  setMode('row');
  updateHighlightMenu();
}

function setHighlightCol(){
  setMode('column');
  updateHighlightMenu();
}

function setHighlightCrosshair(){
  setMode('crosshair');
  updateHighlightMenu();
}

function highlight(type) {
  const ss = SpreadsheetApp.getActive();
  const s = ss.getActiveSheet();
  const a = ss.getActiveCell();

  const col = a.getColumn();
  const row = a.getRow();
  const maxColumns = s.getMaxColumns();
  const maxRows = s.getMaxRows();

  const c = s.getRange(1, col, maxRows, 1);
  const r = s.getRange(row, 1, 1, maxColumns);
  const ar = s.getRange(row, col); 
  
  var ranges = [];
  if (type != 'column') ranges.push(r.getA1Notation());
  if (type != 'row') ranges.push(c.getA1Notation());
  ranges.push(ar.getA1Notation());
  console.log('Ranges ',ranges);
  s.getRangeList(ranges).activate();
  console.log('Active sheet ',s.getName());
  console.log('Active', s.getActiveRangeList());
}

function highlightRow() {
  highlight('row');
}

function highlightColumn() {
  highlight('column');
}

function highlightCrosshair() {
  highlight('crosshair');
};

// Uncomment this function if you need
function onSelectionChange(e) {
  console.log('onSelChange',getMode());
  highlight(getMode());
}

A small addition to https://jec.fyi/blog/highlight-apps-script code

Looking for a "crosshair" highlight function in Google Sheets

5 Answers5