## Sunday, September 22, 2013

### Google Spreadsheet Script to Change Color of Cell(s) Having Minimum Value

Question:

( by Kaido Põder )

I have range A1:A9 numbers. In Google Spreadsheet, How can I change color of cell having minimum value.

Solution:

Have a look at the following screenshot:

Have a look at the following code:

///////////////////////////////////////

// This script has been developed by Kishan

var s = ss.getSheetByName("Sheet1");
var r = s.getRange("A1:A9");

function onEdit(e) {
var activeSheet = ss.getActiveSheet();
var activeRange = activeSheet.getActiveRange();
var sortedValues;
if( s.getName() == activeSheet.getName() &&
activeRange.getLastRow() >= r.getRow() &&
activeRange.getRow() <= r.getLastRow() &&
activeRange.getLastColumn() >= r.getColumn() &&
activeRange.getColumn() <= r.getLastColumn() )
{
check();
}
};

function check() {
var v = r.getValues();
var minValue;
var flag=false;

for(var i=0;i<v.length;i++)
for(var j=0;j<v[0].length;j++) {
if(v[i][j]!="" && flag==false) {
minValue=v[i][j];
flag=true;
}
else if(v[i][j]!="" && minValue>v[i][j]) minValue=v[i][j];
}

var rowcol = [];
for(var i=0;i<v.length;i++)
for(var j=0;j<v[0].length;j++)
if(minValue == v[i][j])
rowcol.push([i,j]);

r.setBackground("White");
for(var k=0;k<rowcol.length;k++)
s.getRange(r.getRow()+rowcol[k][0],r.getColumn()+rowcol[k][1]).setBackground("Yellow");
};

///////////////////////////////////////

put the above code in your script editor and change
Sheet1 with your sheet name and A1:A9 with the range that you want to include to check for the minimum value. If you want to check for whole column A then put "A:A" and if you want to check for column A,B,C then put "A:C", so it will work for any range you provide...

And If you are not much familiar with scripts then check out the following link:

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html

Thanks,