To install this script go to Script Gallery from your Google Spreadsheet and then search for "Kishan's Conditional Formatting" and then install it.
Here is the screenshot of Sheet 'Conditions' of my spreadsheet, from which the script will copy this sheet to your spreadsheet:
And here is the link of this Spreadsheet.
Now, you have to set all the conditions on this Sheet. And make sure you never rename this sheet, as the script will take inputs of conditions from this sheet and perform its execution.
In Sheet 'Conditions' you have to input two cells for comparison, I will refer them as Cell1 and Cell2 for the rest of this post. And after comparing these cells with condition, you can change the background color of any one cell of the spreadsheet, and I will refer this cell as Cell3.
How to input the Cell1 and Cell2?
You have to put the Sheet name of Cell1 in Column A and near to that cell you have to put Cell2, that is in Column B. Then you can input the condition in Column C. And similarly, you need to input Cell2 in Column D and E.
And in Column F and G, you need to input Cell3, (Cell3 can be the Cell1 or Cell2 or any other Cell from the spreadsheet). Then Column H contains the color that you need to format the background of Cell3 if condition is true. And put the color in Column I, for if condition is false.
NOTE: You need to input values only up to Column I and leave the rest. Don't try to edit the Columns J,K,L,M...
When the script executes, it will put the status for each condition in Column J. So if there is any error (like if you don't input correct values in Cell1 or Cell2 or Cell3) then you will get Error message in Column J. And if no error then it will show "Done!"
Column K is for getting the list of all sheet in your current Spreadsheet, so that we can have the drop down list (data validation) in Column A,D and F.
Note: This script will auto execute each and every time you make any edit on your spreadsheet, as I have called function ConditionalFormatting() in onEdit(e) event. If you notice that it has reduced the speed of your spreadsheet then you can comment this one line by having two forward slashes, that is "//", in front of the ConditionalFormatting().
By doing so script will stop execution for each and every edit and then you can manually call this function from the "Conditional Script" Menu on the "Tool Bar".
If you have any further questions, then let me know by posting it in the comments below...
Thanks,
Kishan.
Hello Kishan,
ReplyDeleteThanks for developing this very useful script.
I've just installed it and ran it and I have a question.
I'm comparing two columns (A and B) and formatting column A (green or red) in relation to column B's value (equal is green, different is red). Both columns have the same numbers. After I ran the script I get most cells in column A colored in green but I get also some reds although the numbers are the same between A and B. How come? Do you have any tips?
Thanks again for your work,
Best
M
Thank you, Kishan!
ReplyDeleteHi Kishan, is this still working? I copied the sheet Conditions without changing anything. Unfortunately it doesn't accept any value in the cell A2 where I am supposed to enter the name of the source sheet?
ReplyDeleteOr maybe I just missunderstand your description?
HI Kishen,
ReplyDeleteThanks for creating a script for this, it was much needed! It works just perfectly.
I just wanted to know, I have a situation where in my condition is as below :
C5<G5, turn green
G5<B5, turn red.
How do I achieve that? When try to put 2 conditions, it is just taking the second one, as we have specified 2 colors for true and false. I want to color the field, in case of true and do nothing in case of false.
How do I achieve that?
Thanks,
--
Preeti
Hi Kishen,
ReplyDeleteFirst, I'd just like to mention that this is a fantastic script. One of the best mods for Google Spreadsheets I've ever seen. I've made a small improvement to the ConditionalFormatting function that enables it to apply font coloring as well as the background coloring. This is particularly useful in situations where you need to use the cell's value in the computations, but you don't want any numbers to be visible in the cell itself. You just set the font and background to the same color. ;-)
Anyway, here's the ConditionalFormatting function with the extra feature built-in. Cheers!
function ConditionalFormatting() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.getSheetByName("Conditions").getRange("J2:K").clearContent();
var sheet = ss.getSheetByName("Conditions");
var datarange = sheet.getDataRange();
var values = datarange.getValues();
var value1,value2,flag,status=[];
var colors = datarange.getBackgrounds();
var font = datarange.getFontColors();
for(var i=1;i" ) { if( value1 > value2 ) flag=true; else flag=false; }
else if( values[i][2] == "<" ) { if( value1 < value2 ) flag=true; else flag=false; }
else if( values[i][2] == "equal to" ) { if( value1 == value2 ) flag=true; else flag=false; }
else if( values[i][2] == "not equal to" ) { if( value1 != value2 ) flag=true; else flag=false; }
else if( values[i][2] == ">=" ) { if( value1 >= value2 ) flag=true; else flag=false; }
else if( values[i][2] == "<=" ) { if( value1 <= value2 ) flag=true; else flag=false; }
else NoFunctionButToCreateError();//creating error to go to catch(e)
if(flag==true) {ss.getSheetByName(values[i][5]).getRange(values[i][6]).setBackground(colors[i][7]); ss.getSheetByName(values[i][5]).getRange(values[i][6]).setFontColor(font[i][7]);}
else {ss.getSheetByName(values[i][5]).getRange(values[i][6]).setBackground(colors[i][8]); ss.getSheetByName(values[i][5]).getRange(values[i][6]).setFontColor(font[i][8]);}
status.push(["Done!"]);
}
catch(e) { status.push(["Error!"]); }
}
if(values.length>1) ss.getSheetByName("Conditions").getRange(2,10,values.length-1,1).setValues(status);
allSheets();
};
This comment has been removed by the author.
ReplyDeleteHi Kishan,
ReplyDeleteThanks for this. I have a group of students who have achieved a grade against a prediction. I have given the grades a numerical value which appear in a hidden column when I input their grade. Therefore 4b = 14. I compare what they have achieved (14) against their predicted grade e.g. 5c (12). I want the spreadsheet to tell me whether they have exceeded, equalled or not met this grade.
Following your conditions I entered a range of cells on the Conditions sheet in columns B, E & G (F2:F5 etc). I did this twice as I need to have the three colours green for exceeded, yellow for met and red for not reached.
When I ran it with the two sets of conditions - it didn't work at all. When I removed the second condition, all cells in the column went either red or green, no matter what the individual result said. So instead of having cell 2 red, cells 3 & 4 green and cell 5 red, for example, the whole column was red.
Is there any way round this?? I really need the three colours if it can be done, which of course it can be on an excel spreadsheet. However, we are a 1:1 laptop school and google spreadsheets are what we use......
Any help would be appreciated.
Mark
Hi All, I'm trying to format a sheet in this fashion....
ReplyDeleteIf cell has has Strikethrough and is not bold then background yellow.
I've tried setting it up, f2 a cell that is standard text is not equal to g2 a cell that is strikethrough format an entire column yellow... however this applies all one color to the whole column.
I'm clearly missing something. Is this only going to work for cells with numbers?
Is that possible with this? It doesn't seem possible. Any help would be greatly appreciated.
I need to remove this, but can't see how. I deleted all scripts from manager. It is still on the menu bar. Please help.
ReplyDelete