Simple tricks to get results in Excel or Google Sheet

How to identify two same color cells in a single row in google sheet?

Step 1: Select Cells (A2:A5) -> Format -> Conditional formatting

Step 2: Paste formula =Countif($A$2:$A$5,A2)>1 at Format rules then change formatting style fill color by Color 1 cell fill color.

Step 3: Select Cells (B2:B5) -> Format -> Conditional formatting

Step 4: Paste formula =Countif($B$2:$B$5,B2)>1 at Format rules then change formatting style fill color by Color 1 cell fill color.

Step 5: Goto Extension -> Apps Script

-> Files + -> Script

Clear default function then paste the script

/**
* @param {range} countRange Range to be evaluated
* @param {range} colorRef Cell with background color to be searched for in countRange
* @return {number}
* @customfunction
*/

function countColoredCells(countRange,colorRef) {
  var activeRange = SpreadsheetApp.getActiveRange();
  var activeSheet = activeRange.getSheet();
  var formula = activeRange.getFormula();
  
  var rangeA1Notation = formula.match(/\((.*)\,/).pop();
  var range = activeSheet.getRange(rangeA1Notation);
  var bg = range.getBackgrounds();
  var values = range.getValues();
  
  var colorCellA1Notation = formula.match(/\,(.*)\)/).pop();
  var colorCell = activeSheet.getRange(colorCellA1Notation);
  var color = colorCell.getBackground();
  
  var count = 0;
  
  for(var i=0;i<bg.length;i++)
    for(var j=0;j<bg[0].length;j++)
      if( bg[i][j] == color )
        count=count+1;
  return count;
};

Save Project