google-sheetsformattingconditional-formattinghighlightrichtext

Google Sheets conditional formatting highlight text

Is it possible to automatically highlight part of the text inside the cell if it matches a range of values?

e.g. the column A has these values:

enter image description here

The exclusion list in B has the Deputy Mayor and Member of the Board:

enter image description here

The expected result of conditional formatting:

enter image description here

Solution

  • In Google Sheets, there are three general ways to apply formatting based on certain conditions automatically: using the built-in conditional formatting feature, by using third-party tools or by using code.

    Built-in feature
    The built-in conditional formatting feature options can apply basic formatting, like the cell background color. There are no options to use rich text formatting for parts of the cell content, aka text runs. In other words, it's possible to apply a color to the whole cell content but not to a specific word or phrase.

    Using third-party options
    Look at the Google Workspace Marketplace for add-ons that work with Google Sheets. There may be already one that meets your needs.

    You might search the web using your favorite search engine for independent tools. Some famous tools are Zapier, IFTT, Make.com, but there might be many more.

    It's worth noting that you might ask for a software recommendation in Software Recommendations Stack Exchange.

    Using code

    Using Google Apps Script
    The Spreadsheet Service (Class SpreadsheetApp) of Google Apps Script has classes RichTextValue and RichTextBuilder to handle rich text formatting of cell content. The Advanced Sheets Service also has ways to do the same based on the Sheets API.

    Using the Sheets API
    With the Sheets API it's possible to apply rich text formatting by using other platforms like Node.js and programming languages like JavaScript, Python, Java and others—specifically, use TextFormatRun.