Google sheets conditional formatting to colour cell if the same cell in another sheet is not empty

1
votes

I have 2 sheets A and B. I want sheet A to colour cells if the exact same cell in sheet B is not empty. For example if cell A1 in sheet B has a string, then cell A1 in sheet A will be coloured.

Let's say I want to do this to compare a large range, how should I go about doing this?

Example: https://docs.google.com/spreadsheets/d/1P3Ob_mclpXWmILfKwD4R6JN2wAYPUcNZlmtF9LxilV0/edit?usp=sharing

Cells A2:C2, D5 and K2 in sheet B are not empty. So the corresponding cells in sheet A will be coloured red.

2
google-sheetsgoogle-sheets-formulaconditional-formatting
See this article: https://productivityspot.com/conditional-formatting-based-on-another-cell-google-sheets/ – NKSM
@NKSM Nope, that article refers to within sheet cells. What I need is a code to compare to a cell in another sheet. – Portarto
And this one: https://www.extendoffice.com/documents/excel/4734-google-sheets-conditional-formatting-based-on-another-sheet.html – NKSM
And another one: https://support.google.com/docs/thread/4490297?hl=en. – NKSM
@NKSM thank you, but it still does not solve my problem. The match function does not work with empty cells in sheet A. – Portarto

2 Answers

5
votes

You can use the following formula as a conditional formatting rule:

=NOT(ISBLANK(INDIRECT("B!"&address(row(),column()))))

(the formula in A!A2 is there to just show you what happens in sheet B)

enter image description here

1
votes

Explanation:

Not familiar how this could be done with a custom conditional formatting formula so wait for other guys to help you out with that.

However, I can offer you a Google Apps Script solution.

The following script will check for the data range in sheet B and it will construct a color array. An element of this array will have the white hex color #ffffff" if the value in the values array is empty, or a red color #ff0000 if the value is not blank.

Then it will color sheet A based on this color array in one go. The script is relatively fast even for large ranges.

Solution / Workaround:

Don't forget to adjust "sheet A" and "sheet B" to the name of your sheets.

function colorCells(){
  const ss = SpreadsheetApp.getActive();
  const sheetToColor = ss.getSheetByName("sheet A");
  const sheetToCheck = ss.getSheetByName("sheet B");  
  const bValues = sheetToCheck.getDataRange().getValues();
  const aColors=bValues.map(r=>r.map(v=>v==''?"#ffffff":"#ff0000"));
  sheetToColor.getRange(1,1,sheetToCheck.getLastRow(),sheetToCheck.getLastColumn()).
  setBackgrounds(aColors);
}

How to use that:

Click on Tools => Scipt editor, copy, paste & save the aforementioned code and then execute it by clicking on the play button.

If you want this to work "automatically" then you can set up a time trigger to execute this function for you every n minutes.

Bonus code:

You can do the same operation but on a more user-interactive way. Again, copy, paste & save the following script to the script editor but this time don't execute it as it can work by itself.

The following script will change the color of a cell in sheet A upon user edits on the same cell in sheet B:

function onEdit(e) {  
  // adjust this to your needs:
  const sheetToColorName = "sheet A";
  const sheetToCheckName = "sheet B";
  const rng = e.range;
  const cell = rng.getA1Notation();      
  if (rng.getSheet().getName()==sheetToCheckName){
    const sheetToColor = e.source.getSheetByName(sheetToColorName);
    if(rng.getValue()!=''){        
        sheetToColor.getRange(cell).setBackground("#ff0000"); //red     
    }
    else {
        sheetToColor.getRange(cell).setBackground("#ffffff"); //white
    }    
  }
}