Google Sheets – How to Use Cell Reference with Color Formatting

formattinggoogle sheetsgoogle-apps-script

Is it possible to reference a cell in Google Sheets so that cell where it gets displayed also displays it using the same text and cell colour formatting?

=A1

Will only reference cell's value. But if that particular cell has red background and white text I'd like that to be copied as well.

I'm leaning toward existing formula solutions rather than scripts. If applicable of course.

Best Answer

For Google Spreadsheets, it is possible by writing a script:

function copyValuesAndFormatting() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet();

    var fromRange = sheet.getRange("A2:A");
    var toRange = sheet.getRange("B2:B");
    var values = fromRange.getValues();
    var fontColors = fromRange.getFontColors();
    var backgrounds = fromRange.getBackgrounds();
    var fonts = fromRange.getFontFamilies();
    var fontWeights = fromRange.getFontWeights();
    var fontStyles = fromRange.getFontStyles();

    toRange.setBackgrounds(backgrounds);
    toRange.setFontColors(fontColors);
    toRange.setValues(values);
    toRange.setFontFamilies(fonts);
    toRange.setFontWeights(fontWeights);
    toRange.setFontStyles(fontStyles);
}

Add a trigger for the script function, so that it runs on every spreadsheet modification.

I have created a sample spreadsheet here. Feel free to copy it to your own account, and start experimenting with it.

Related Solutions

Google-sheets – Conditional formatting based on cell values in same column

Assuming the actual finishing date is in Column F and the sheet is called Sheet1, paste the code below inside your spreadsheet under Tools >> Script editor, then hit Save.

/***
* Color Code+ 
* Conditional formatting with cell referencing for Google Spreadsheets
* Support: scripts@bryanp.com
**/

function onEdit() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName('Sheet1');
var values1Rule1 = s.getRange('E1:E').getValues();
var values2Rule1 = s.getRange('F1:F').getValues();
var range3Rule1 = s.getRange('E1:E');
var color1 = 'red';

for (var row in values1Rule1) {
for (var col in values1Rule1[row]) {
if (values1Rule1[row][col] == values2Rule1[row][col]) s.getRange(s.getRange('E1').offset(row, col, 1, 1).getA1Notation()).setBackgroundColor(color1);
else s.getRange(s.getRange('E1').offset(row, col, 1, 1).getA1Notation()).setBackgroundColor('white'); }}
};

I used this script generator to make creating the code easier. You can adapt it to your needs.

Google Sheets – Use Conditional Formatting Based on Another Column

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.

Best Answer

Related Solutions

Google-sheets – Conditional formatting based on cell values in same column

Google Sheets – Use Conditional Formatting Based on Another Column

Related Topic