Google sheets conditional formatting not stacking

user_19257824 0
Viewed 123

I have a set of conditions using a simple custom formula to set row color based on the content of column A cell: =$A2="Tag1". This works great. But I also want to change the text color of any cell that contains a particular string (e.g. WARNING). So I created a separate condition for the entire spreadsheet using custom formula: =A2="WARNING". This works great as well - by itself. But it does not stack with the row color conditions. If it's first in the list, no matching cell text is recolored for rows with color changes. If I put it last, all match cell text is recolored, but the affected cells lose the changed background color. I understand why this happens, but I don't know how to correct it. I thought about using more than one condition, but custom formula does not seem to support that (or rather, any required combinations must be part of the custom formula).

Any ideas would be appreciated.

conditional-formatting google-sheets
1 Answers

According to this Google Docs article, the first rule found in the conditional formatting to be true will define the format of the cell or range.

A workaround would be to use Apps Script, I have created the following script to give you an idea of how you can apply it.

function onEdit(e){ //this function will run whenever someone edits the sheet
  var s = SpreadsheetApp.getActiveSpreadsheet();
  var changedCell = e.range.getA1Notation();
  if (changedCell !== 'A2') return;
  
  else{
    var value = s.getRange("A2").getValue();
    
    if (value == 'Tag1'){ //check if content equals to 'Tag1'
      background = 'red';
      s.getRange("A2:D2").setBackground(background); //specify the range that you want to change background
    }
    
    else{ //if content in A2 is different than 'Tag1' then set background as null
      s.getRange("A2:D2").setBackground(null);
    }
  }
}

The above script will only change the background if content in A2 = "Tag1". After adding this script, you can create a conditional formatting for custom formula: =A2="WARNING". This way it will apply both conditions if both rules are true.

You can also request the option of stacking conditional formatting by going through a Google Sheet file, click on ‘Help’ > ‘Help Sheets improve’ and give a description of your request.

user_179264780
Related