/docs/community?hl=en
/docs/community?hl=en
9/8/12
Original Poster
bryan-p

Color Code+ ...Conditional formatting (with cell referencing)

Hi, I wrote this web app (Color Code+) with apps-script to be a solution for those that need conditional formatting with the ability to reference cells or ranges.  Throw in some rules and it'll spit out code that you can paste in your spreadsheet under Tools < Script editor.  

Some examples of what it can do.  (Make a copy first).  Here's the main issue thread on what it's trying to address, but if you find it still doesn't fit your case, then you can contact me on G+ or just post a request for some thing here in this thread. 

- Bryan

Community content may not be verified or up-to-date. Learn more.
All Replies (58)
meph2u
9/9/12
meph2u
This is great.   Signing up for this thread to find out what happens next.

I edited out the "else" clauses to allow for successive applications of rules to the same range so that multiple colors could be assigned.

See:

https://docs.google.com/spreadsheet/ccc?key=0Atrb4wVf_dsSdE1RdTYtM1piUzVFUXRGOThtckI2Y3c#gid=0 

and

https://docs.google.com/spreadsheet/ccc?key=0Atrb4wVf_dsSdGlvSFk5Nlg1WDJ0dXNWcFpCYkp2MkE#gid=0 

Thank you so much.  I can't tell you how much I will be using this.
meph2u
9/9/12
meph2u
Brian,

I would like a custom function that worked like this

=mycolorfunction(value,color) which would place the value in the cell at set it's background color

So it would work as simply as:

=mycolorfunction("Color me Green","Green")

or

=mycolorfunction(A1,A2) where A1 has the text to be displayed and A2 had the color to turn the background.

All the logic of which color to display would then be put into A2

Is this crazy?
9/9/12
Original Poster
bryan-p
@meph2u - your welcome and thanks for that screen shot and examples.  Can you try this code?  Had not accounted for multiple coloring rules for the same ranges yet, but I think that link has what you need for your ss.  It does include an Else at the very end to turn cells white if they don't meet any of your conditions though.  

I do have plans to offer multiple conditions within the same rule (ex. IF A1 == B1 AND A1 == C1 THEN ...) so that circumstance is on the radar, but I'll probably also consider spitting out better code for ranges that get repeated like in your situation.  Let me think about your next question and I'll get back to you.
meph2u
9/9/12
meph2u
WOW.   Thanks.   The code works perfectly!   I also think I am following it.  

I am going to create some similar situations and see if I can generalize it a bit.

Between this and what your generator provides I will be able to code the color on a convoluted sheet I think.  If you would like to see it in the interest of science I'd be excited to show it to you. 

You plans sound very useful and I look forward to them.

I appreciate you spending some time to think about the function.   Of course, I had to resist making it more complex as that simple case would solve so many problems for me (I can just hide all the background values on a different sheet).
9/9/12
Original Poster
bryan-p
=mycolorfunction(A1,A2) where A1 has the text to be displayed and A2 had the color to turn the background. 

So, this seems to be already taken care of with the regular Text Background Color icon, right?  You just need to type the text you want into a cell and clicking that format icon.  No conditions or comparisons going on.

But trying to use a function to do any formatting returns a permission error anyhow.
meph2u
9/9/12
meph2u
Thanks for trying.   If there is a permission error (or other sort), then the idea won't work.

However, using the Icon requires manually doing this (and there are 100s of cells in my real life example that each need to be manually changed each time the status value changes.

Just to make sure I have communicated clearly, I have developed an example. 

https://docs.google.com/spreadsheet/ccc?key=0Atrb4wVf_dsSdDhwWU0yS210TzRXLXh2cEwzS0ZlT1E#gid=0 

Thanks again.
meph2u
9/9/12
meph2u
I found a thread from a year ago that says you can can not set background color of cells using custom function.  It references an obsolete documentation page.  But based on your error, I am guessing this is still the case.   Drat (and thanks again).

meph2u
9/9/12
meph2u
The current page of custom functions does not mention restrictions on setting background color among the permission restrictions.   Probably just incomplete documentation.

https://developers.google.com/apps-script/execution_custom_functions 
9/9/12
Original Poster
bryan-p
I can have my script go off your cells that contain the changing color names and use those values to update the formatting rules.  That's what you're saying you need, right?
meph2u
9/9/12
meph2u
Yes - that is it (I think :-))

I realize it has to be a sheetwide script.
47 MORE
Trevor Woodcock
11/15/13
Trevor Woodcock

My suggested solution to the lack of a professional Conditional Formatting facility in Google Spreadsheets is :-


Create a "helper" sheet in your spreadsheet , which mirrors the format of the main sheet.

Put formula in the cells of the helper sheet which refer to cells in you main sheet.


These formula return results of your choice which will be subsequently tested by Conditional Formatting applied to the cells in the helper sheet.


Use the Script Editor to create a script which copies the background colours of the appropriate cells in your helper sheet to those on the main sheet.


I have now used this workaround on two more of examples and I share my solution spreadsheets with you :-


Walt's Example 1

Walt wanted to colour-code the background colours of alternate rows, but in addition, he wants to be able to sort the data.

With standard Google functionality the the resulting alternate row colouring is lost when you perform a filter/sort on the range. So a solution involving a Helper sheet is ideal, as the colouring on the helper sheet is unaffected by the sort, so that the alternate colouring is restored by the script.

In earlier examples examples, which I have included in this blog for completeness,I used an onEdit function to fire the code. That did not work in this example, since Google does not count a sort operation as an edit.

I have therefore explored the creation of an onChange function, which in this example looks like this.

// You need to run this function from the script edit

// whenever the MyonChange function is modified.

// (See below for the MyonChange or onEdit code).

function CreateMyonChange() {

var sheet = SpreadsheetApp.getActive();

ScriptApp.newTrigger("MyonChange")

.forSpreadsheet(sheet)

.onChange()

.create();

};

Walt's Example 2

This example shows similar code operating on several sheets.

Other examples which I have taken from this forum, or have been requested directly are:-

Davids Example

David has weekly data in up to 52 weeks across each row, and wants to colour code each cell depending on whether the value is lower, equal or higher than the previous cell.


Guys Example

Guys example is quite unusual.


Guy wanted to place a number in A1 which caused the appropriate row in column B to be highlighted.


e.g If A1 contains 1, cell B1 is highlighted.

if A1 contains 7, cell B7 is highlighted.


ChiragExample

Colour of Completed Cell depends on Overdue Cell


JeremyExample

Colour of Cash Outflow depends on Deposit


KelemvorExample

Running total of cost turns red if the budget is exceeded.


sethExample

Highligh cell if it is older than today minus a given number of days.


Some of my example solutions format the background colours and others the font colours, others both. The technique would also be very effective where there are more than one conditional formatting requirements on the same page, as you would normally only need to set the copy range to suit.


For example :-

// function MyonChange() { // (see Walt's examples )

function onEdit() {


// This code retrieves the Background colour from D2:AJ100 in Sheet2

// and uses these to set the Background colour of D2:AJ100 in Sheet2.

// If you uncomment the code related to Font colours these will

// also be copied.

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheetByName('Sheet2');

var range = sheet.getRange("D2:AJ100");

var bgColors = range.getBackgrounds();

// var FontColors = range.getFontColors();

sheet = ss.getSheetByName('Sheet1');

var cell = sheet.getRange("D2:AJ100");

cell.setBackgrounds(bgColors);

// cell.setFontColors(FontColors);

};



Simply comment out either the bgColor or FontColor lines if you do not need them.



Were these replies helpful?
How can we improve them?
 
This question is locked and replying has been disabled. Still have questions? Ask the Help Community.

Badges

Some community members might have badges that indicate their identity or level of participation in a community.

 
Expert - Google Employee — Googler guides and community managers
 
Expert - Community Specialist — Google partners who share their expertise
 
Expert - Gold — Trusted members who are knowledgeable and active contributors
 
Expert - Platinum — Seasoned members who contribute beyond providing help through mentoring, creating content, and more
 
Expert - Alumni — Past members who are no longer active, but were previously recognized for their helpfulness
 
Expert - Silver — New members who are developing their product knowledge
Community content may not be verified or up-to-date. Learn more.

Levels

Member levels indicate a user's level of participation in a forum. The greater the participation, the higher the level. Everyone starts at level 1 and can rise to level 10. These activities can increase your level in a forum:

  • Post an answer.
  • Having your answer selected as the best answer.
  • Having your post rated as helpful.
  • Vote up a post.
  • Correctly mark a topic or post as abuse.

Having a post marked and removed as abuse will slow a user's advance in levels.

View profile in forum?

To view this member's profile, you need to leave the current Help page.

Report abuse in forum?

This comment originated in the Google Product Forum. To report abuse, you need to leave the current Help page.

Reply in forum?

This comment originated in the Google Product Forum. To reply, you need to leave the current Help page.