-1

How can I use conditional formatting by using the note on a specific or many cells in Google Sheets? For example, I would like to set up conditional formatting to a specific cell and when I use a note on that cell it will automatically change the color.

  • not possible by without script – user0 Jan 27 at 14:37
0

you can use this script to export your note into some side cell and then use simple conditional formatting to check if that cell is empty

function NOTE(cell)
{
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var range = ss.getRange(cell)
   return range.getNote();
}

  • formula: =NOTE("A1")

  • custom formula for conditional formatting: =IF(D$1:D<>"", 1)

0

The built-in conditional formatting feature of Google Sheets can't be used to directly format cells based on notes content but you could use and add-on or Google Apps Script to do a conditional formatting tool.

Example:

This function runs every time a cell/range is edited. If the cell/range has a note the cell/range background color will be set to green.

function onEdit(e){
  if(e.range.getNote()){
    e.range.setBackground('green');
  }
}

NOTE: The onEdit simple trigger isn't triggered by inserting a note. As an alternative of a simple trigger you could make a script that check all the sheets cells calling it from a custom menu, from the script editor, an image or custom dialog/sidepanel.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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