2

I have been working on a spreadsheet in Google Sheets that serves as a database of the hundreds of characters who appear in a novel I am reading (to help me keep track of them all). Column A contains the characters' names, while Column B contains their factions and/or loyalties. The data in Column B is color-coded, based on characters' most recent faction, using conditional formatting.

I have also been manually coloring characters' names in Column A to match the colors of their respective Column-B data. While this takes little extra effort or time, given the sheer number of characters I am adding each chapter, it does add up quickly. As such, I have been exploring the possibility of using conditional formatting to automatically color the data in Column A to match the corresponding data's colors in Column B. However, I have been unable to find any built-in formula that gets any form of text formatting from cell data, which is not terribly surprising.

Basically, I would like to know if there is any existing formula I could use to accomplish this: recoloring a cell in Column A based on the color of the corresponding cell in Column B. Be aware that the color it should be getting is the color of the Column-B text after existing conditional formatting is applied.

If not, what would an Apps Script for accomplishing this look like, and how would I go about implementing it? (I have no prior experience using scripts to add functions to Google Docs editors.)

1

You cannot use the formatting of a cell as input to a formula.

But, it is easy to extend the range of conditional formatting so that it applies to A as well as to B, while being based on the data from B.

For example, formatting the range A2:B according to the custom formula =LEN($B2) colors both A and B if the column B cell is nonempty.

More generally, if the column references in your conditional formatting formula are absolute (like $B above), the result will be the same for all cells within the same row within the formatted range.

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.