这里有一个带有条件格式的选项。我不知道我是否完全理解了您对橙子和红色的要求，但这里有一个您可能能够适应的选项
如果所有的选项都匹配，它将返回绿色。这是通过拆分每个单元格并将其与A1中的值进行比较来完成的。通过使用REGEXMATCH对筛选的数据进行计数，并与两个单元格中的项目数量进行比较，您将能够知道每个选项是否匹配
到橙子你会有类似的东西，但没有最后一步
对于红色，您可以将NOT与REGEXMATCH结合使用

Green: =LET(v,SPLIT($A$1,",",1,1),a,SPLIT(A3,",",1,1),(COUNTA(IFNA(FILTER(a,REGEXMATCH(a,JOIN("|",v)))))=COUNTA(v))*(COUNTA(v)=COUNTA(a)))

Orange: =LET(v,SPLIT($A$1,",",1,1),a,SPLIT(A3,",",1,1),COUNTA(IFNA(FILTER(a,REGEXMATCH(a,JOIN("|",v))))))

Red: =LET(v,SPLIT($A$1,",",1,1),a,SPLIT(TO_TEXT(A3),",",1,1),COUNTA(IFNA(FILTER(a,NOT(REGEXMATCH(a,JOIN("|",v))))))=COUNTA(a))

备选方案：

您可以尝试使用Apps Script基于引用单元格设置列的格式。请根据提供的工作表查看下面的脚本。
1.单元格引用位于单元格A1上
1.该脚本应用于Column B2:B中的值

脚本：

您可以使用此示例脚本作为问题的基线。

function applyConditionalFormatting() {
  
  var sheet = SpreadsheetApp.getActive().getActiveSheet(); //get the active spreadsheeet
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange("B2:B" + lastRow); //declaring the column in which the script will be applied to along with last row with values
  var numRows = range.getNumRows();

  var orangeBackground = '#FFA500';
  var greenBackground = '#00FF00';
  var redBackground = '#ff0000';

  var criteria = sheet.getRange("A1").getValue().toString().split(",");   //getting the value of reference cell

  for (var row = 1; row <= numRows; row++) {
    var cell = range.getCell(row, 1);
    var value = cell.getValue().toString(); // iterating on every value on the range provided

    var hasCriteria = [];
    for (var i = 0; i < criteria.length; i++) {
      var currentCriteria = criteria[i].trim();
      hasCriteria[i] = value.indexOf(currentCriteria) >= 0; //checking if each of the cell value has either "B" or "D"
    }
    var allCriteriaMatched = hasCriteria.every(function (c) {
      return c; //checking if each of the cell value has either "B,D" or "D,B"
    });

    if (allCriteriaMatched) {
      cell.setBackground(greenBackground); //setting the color to green for full match
    } else if (hasCriteria.some(function (c) {
      return c;
    })) {
      cell.setBackground(orangeBackground); // setting the color to orange forp partial match
    } else {
      cell.setBackground(redBackground); //setting the color to red if neither
    }
  }
}

输出：

注意：我只是在工作表中添加了一个按钮，并附加了上面的脚本用于演示目的

参考资料：

https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/indexOf
https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/every
https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/some
https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app