I used the following script proposed in a different question (How can I change the background color of a cell based on the cell contents using a color lookup sheet?) to change the background and font color of cells with certain values. If I put them in manually, it works. If the cells get filled with either =filter() or =query(), the colors won't change. Anybody has a solution for that?

function onEdit(e) {
 if ((e.range.getSheet().getSheetName() == 'Standings') || (e.range.getSheet().getSheetName() == 'Conference Standings')) {
 var values = e.source.getSheetByName('Teamcolors').getRange('A:D').getValues();
var i = 0;
while (i < values.length && values[i][1]) {
  if ((values[i][0] === e.value) || (values[i][1] === e.value)) {
    color = values[i][2];
    fontcolor = values[i][3];

On the sheet "standings" the team names are put in by hand. There the script works and the cells are colored based on the team name. On the sheet "conference standings", a query is used to filter and sort the team list. There the cells colors are not changed. It is also not a spelling mistake. As soon as I enter the team names manually to "conference standings", the colors are updated!

Your Answer

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

Browse other questions tagged or ask your own question.