I already published an article about color coding for SharePoint lists three months ago, and its sibling for calendar views last month. Both rely on the same client side method, the “HTML calculated column“.
Color coding involves 3 steps:
1/ Select the color
2/ Apply the formatting: traffic light, font color, background color, etc.
3/ Include the magic script that renders the formatting (SharePoint OOTB would just display text)
People are usually comfortable with step 3, which is just a copy/paste of a generic script. But some users are struggling with the first two steps, because they are not so familiar with SharePoint calculated columns.
So I have decided to publish a list of examples based on the default SharePoint lists. They can be used as is and should cover a wide range of use cases. For each example, you can grab the formula by clicking on “copy to clipboard”.
If you are a seasoned list designer, skip this post…but forward the link to your friends!
1/ Color selection
In HTML, you can identify a color by its name or its code. Here we’ll stick to the friendly color names. A good reference is W3schools.
Let’s call [Color] the column that will store the value. You can choose among the following column types:
- Text
- Choice
- Calculated column
The latter will be the most useful, as the color usually depends on another factor. For example, we’ll pick red for issues that are tagged as “Critical”.
If you are not familiar with calculated columns, you can start by reading this introduction.
Example 1: Priority (Issues list, Tasks list)
(1) High red
(2) Normal gold
(3) Low limegreen
Use this formula in your [Color] calculated column:
=CHOOSE(RIGHT(LEFT(Priority,2),1),"red","gold","limegreen")
How it works: if the user selects “(2) Normal”, RIGHT(LEFT(Priority,2),1) will extract the second character. It is the number “2″, so CHOOSE will select the second item – gold.
Example 2: Category (Issues list)
(1) Category1 red
(2) Category2 gold
(3) Category3 limegreen
The formula in this case:
=CHOOSE(RIGHT(LEFT(Category,2),1),"red","gold","limegreen")
Example 3: numbered list
Let’s say you have a [Choice] column with following values:
1. First choice red
2. Second choice gold
3. Third choice limegreen
In this case, the number is in first position, and we get a shorter formula:
=CHOOSE(LEFT(Choice,1),"red","gold","limegreen")
Example 4: Issue Status (Issues list)
Active red
Resolved gold
Closed limegreen
No number to help us identify the choice, so we need to find a trick. My choice: identify the position of the letter “e”. If for example “e” is in fifth position, it means that the status is “closed”.
The formula:
=CHOOSE(FIND("e", [Issue Status],1),,"gold",,,"limegreen","red")
How it works: the CHOOSE function will pick a color according to the position of the letter “e”. So if the status is “Closed”:
FIND(“e”, [Issue Status],1) = 5 (the letter “e” is in fifth position)
So the CHOOSE function will pick the color that is in fifth position, “limegreen” here. Note that in our example the letter “e” is never in position 1, 3 or 4, this is why I didn’t enter any color name for these positions.
We can also use nested IFs for a similar result, but the formula is heavier (especially if we have a longer choice list):
=IF( [Issue Status]="Active", "red", IF([Issue Status]="Resolved", "gold", "limegreen"))
Example 5: Status (Tasks list)
Not Started red
In Progress gold
Completed limegreen
Deferred black
Waiting on someone else darkorange
Here again we’ll use the “e” letter trick:
=CHOOSE(FIND("e",[Status],1),,"black",,,,"limegreen",,,"gold","red",,,,,"darkorange")
Here, FIND(“e”,[Status],1) can only take five values:
2 for Deferred –> black
6 for Completed –> limegreen
9 for In Progress –> gold
10 for Not Started –> red
15 for Waiting on someone else –> darkorange
2/ Formatting
After setting the value of the [Color] column, we can now format it. For this we need another calulated column. The formulas below are the same as the ones I used in the initial post on color coding.
Example A: traffic lights
="<DIV style='font-weight:bold; font-size:24px; color:"&Color&";'>•</DIV>"
I am using a bullet (•) to create the shape.
Example B: font color
My formula is for the [Issue Status] column, adapt it if you work with another column:
="<DIV style='font-weight:bold; font-size:12px; color:"&Color&";'>"&[Issue Status]&"</DIV>"
Example C: background color
Here too, replace [Issue Status] with your own column:
="<DIV style='font-size:12px; background-color:"&Color&";'>"&[Issue Status]&"</DIV>"
3/ The script
To apply the script, simply follow the instructions for list views or calendar views. There’s also a troubleshooting page to help you out.
Please let me know if you find that detailed posts like this are helpful.
72 comments
Comments feed for this article
December 12, 2008 at 1:38 am
Charlie Epes
Christophe:
This helped me again to create a wonderful simple In/ Out Board for a team in our company! Thank you.
On another issue, and somewhat a mix of your red/green progress bar AND your data that feeds a sparkline,
- I have 7 date fields
- when a date is current (or in the future), I want to trigger more green in a progress bar
- if a date has past, I want to trigger red
- I need the progress bar to visually reveal how “current” an item is according to the 7 dates
Thanks
Charlie
November 26, 2009 at 7:03 am
Philipp
Hi,
another great thing would then be, to hide the calculated column(s) in the DispForm.aspx…. Is there any possibility??
At the moment there’s the HTML-code, I could set the CEWP there to have the color coded, but better would be not to have it there…
December 12, 2008 at 9:54 am
Christophe
As usual for such requirements, the issue is that the calculated column doesn’t know the current date.
I suggest that you take a look at Alexander’s countdown, which uses the same script and includes the current date (his script uses 3 colors):
http://pathtosharepoint.wordpress.com/2008/11/24/countdowns-a-second-method/
The live demo is here:
http://8002.freesharepoint2007.com/Lists/Tasks/AllItems.aspx
December 16, 2008 at 3:07 am
Christmas indicators « Path to SharePoint
[...] you need to create a calculated column for the color. See my previous post for this. In my example I have used the Priority column of a tasks [...]
January 22, 2009 at 2:42 pm
Brian
Love the site! However, I’m stumped on a very basic custom list, just to test. I’m using nested IF’s (I just can’t wrap my mind around the CHOOSE function…yet) to indicate a color (red, green). The column my IF’s reference is “Priority.” Unfortunately, when I try to enter the second calculated column with the DIV tags, SP keeps telling I’m referring to a column that does not exist, when in fact it does. I’ve looked over the syntax and can’t find any problems. Maybe you can help me.
Here is the IF statement in a calculated column called “CalcColor”
=IF([Priority]=”High”,”Red”,”Green”)
Here’s the DIV statement I want in a calculated column called “CalcColorDiv”
=”"&[Priority]&”"
I don’t understand it. The first calculated column “CalcColor” works just fine and indicates the color properly. But for some reason, the second, “CalcColorDiv” won’t recognize [Priority] as a valid column. I’m sure it’s something small I’m missing. Any help is appreciated. Many thanks.
January 22, 2009 at 3:01 pm
Brian
Nevermind! Sorry everyone with all the comments. Even though both of my earlier posts had the second code sample cut off, I figured it out. I truly was referring to a non-existant column, since I copied Christophe’s sample. My color is contained in a column called “CalcColor” and not “Color,” which is what is used in the example. Sorry about my lack of attention to detail.
January 28, 2009 at 7:02 pm
Brian M.
Christophe,
My next requirement puts me between the “Choose” and nested “IF” methods. I have a status list with nine options. “E” is in the fifth position for two of the choices. What should I try next, a different letter in a different position? Some of the choices also have spaces in between, E.G. “In Transition”.
We’d rather not use any of the “numbering” lists.
Thanks,
Brian
January 29, 2009 at 1:27 am
Christophe
Brian: a first approach would be to use two criteria, for example word length and position of the letter “e”.
As an example of combined criteria, see this post and my comments:
http://www.endusersharepoint.com/?p=1047
However, in your case numbering may be the right approach. So the first step is to understand why you don’t like numbering. Is it just for display reasons? In this case, you could use a calculated column – just for display – that removes the numbers.
January 29, 2009 at 3:43 am
Brian Merrifield
I may go that route, I had the coloring working based on numbers in a choice column. Then, would I create another calculated column to “shift” everything three spaces to the right? (drop the “1._” – space is the underscore)?
Brian
January 29, 2009 at 4:09 am
Christophe
Exactly. Use the RIGHT or REPLACE functions for this.
January 29, 2009 at 4:15 am
Brian M.
I got the last part working using:
=RIGHT(Choice,LEN(Choice)-3)
Thanks Christophe, I think we can get by with this.
January 29, 2009 at 4:28 am
Christophe
Perfect! And thanks for sharing this slick formula, it’ll certainly be helpful for others.
January 29, 2009 at 4:53 am
Brian M.
So, playing around with the formatting and I was looking at changing the “icon” of the traffic light. It appears you could use any of the special HTML characters and symbols. For my experiments I used the following “traffic light” formatting :
A good list of characters and symbols are at these links:
http://www.w3schools.com/tags/ref_symbols.asp
http://www.w3schools.com/tags/ref_entities.asp
February 17, 2009 at 12:12 am
Apply color coding to your SharePoint lists « Path to SharePoint
[...] check out this post for more step by step [...]
February 17, 2009 at 5:41 pm
Brian M.
Christophe,
What if I add more than ten entries to my “status” column (using example #3, a numbered list)? The tenth option no longer displays the correct color.
Thanks,
Brian
February 18, 2009 at 2:54 am
Christophe
Brian: that’s right, the above formula only takes the first digit.
In your case you need to identify the position of the dot first. Use the SEARCH function for this:
=CHOOSE(LEFT(Choice,SEARCH(“.”)-1),…)
February 18, 2009 at 3:04 am
Brian
We could live with the #10 choice being the same color as #1 (FIND 1), so it still works out for us.
Thanks,
Brian
February 18, 2009 at 3:11 am
Christophe
OK. Well, keep this for when you add #11
February 19, 2009 at 2:22 pm
Ben Schlaepfer
Hi Christophe,
I have been using your calculated colums for colour coding quite a bit lately – thanks!
I have a strange problem when trying to replicate Excel conditional formatting in a list.
The column is called DecFormat and contains a range of numbers between 0 and 1.29 OR it may contain an asterisk ‘*’ or a hyphen ‘-’.
The formatting rules are:
If cell value begins with ‘*’ then green bg black text
If cell value begins with ‘-’ then white bg black text
If cell value between 1 and 1.29 then green bg
If cell value between 0.9 and 0.999 then amber bg
If cell value < 0.9 then red bg
I thought this would be easy enough to do with the following:
=”=0.9,[DecFormat]=1,[DecFormat]<=1.29),”green”,
IF([DecFormat]”
Of course the >= does not work if the column is a Single Line of Text and I lose all my ‘*’ and ‘-’ if I convert it to a Number (but then the colour coding for the >= and < for text columns?
Cheers
Ben
February 19, 2009 at 2:35 pm
Ben Schlaepfer
="<DIV style='width:100%;background-color:"
&IF([DecFormat]="*","green",
IF(AND([DecFormat]>=0.9,[DecFormat]<=0.9999),"orange",
IF(AND([DecFormat]>=1,[DecFormat]<=1.29),"green",
IF([DecFormat]<0.9,"red","blue"
))))&";'></DIV>"
:-S
February 19, 2009 at 2:50 pm
Christophe
Ben, your formula looks OK, except for the last single quote (should be a standard quote, not ’ ). Maybe the style change happened when you copied it in the comments?
February 19, 2009 at 3:52 pm
Ben Schlaepfer
Christophe,
Yes it works if the column is of type Number but I have issues that the imported data mixes types – sometimes it’s a number sometimes it’s text i.e. * or – …. so to accomodate both types of data the SharePoint column has to be Text.
So the question is, is there a way that we can think of that a Text column (even if it has a number in it) be evaluated to meet the number range criteria?
February 19, 2009 at 3:59 pm
Christophe
Oh, I see. Check out the VALUE function: VALUE(text) returns the number equivalent of the string.
February 19, 2009 at 4:42 pm
Ben Schlaepfer
Great – that should do what I want: it works on a simple greater than, but when I try to use with an AND I don’t get the results I expect…
I am sure it’s just my lack of syntax knowledge … i.e how do apply a VALUE to the second part of the AND ?
…
IF(AND(VALUE(NovFormat)>=0.9,(NovFormat)<=0.9999),"orange",
…
SharePoint saves that formula OK but the results don’t evaluate.
Thanks for your help!
Ben
February 20, 2009 at 12:13 am
Christophe
You have to apply VALUE to each:
AND(VALUE(NovFormat)>=0.9,VALUE(NovFormat)<=0.9999)
February 26, 2009 at 1:28 pm
Highlight rows in SharePoint lists « Path to SharePoint
[...] The first possibility is to enter the color name directly in the formula – yellow for example. The second option, if you need conditional formatting, is to pull the color name from another column. You’ll find several examples in this post. [...]
March 2, 2009 at 5:58 pm
Michael
Christophe,
I’ve got all this working and it’s an awesome mod. I’m not much of a coder so I could use some help here to go further.
Now that we have Sharepoint columns displaying as HTML, is there some way that we can make the bullet a hyperlink so when it is clicked, it can take the user to some specified document library or other place?
Thanks,
Michael
March 2, 2009 at 8:40 pm
Michael
Ha! i actually got it working from many of your examples on your blog. Many thanks! I have a colored bullet that is also a hyperlink.
My only problem now, is that it underlines the bullet and colores it dark blue. I’ll work with it to see what I can do but if you have an example that will speed my process, I’ll take it. Below is what is working for me.
=CONCATENATE("<DIV style='font-weight:bold; font-size:24px; color:"&CHOOSE(RIGHT(LEFT(CostStatus,2),1),"red","orange","green")&";'><a href='","https://mysite.com/sites/mycustomers/web%20files/Forms/AllItems.aspx",,"' rel="nofollow">•</a></DIV>")Michael
March 3, 2009 at 6:04 am
Christophe
I think simply adding:
text-decoration: none;
in your style should work.
Instead of the bullet, which is considered text, you could also choose to use pictures, like in my KPI example.
March 3, 2009 at 7:24 pm
Michael
Christophe, thanks again for your help. I will eventually try to figure out how to set a different URL for different items in the column.
Michael
March 5, 2009 at 6:18 pm
Michael
I’m using images instead of the text because I could not get any style to work to eliminate the underline of a hyperlink.
Now, I can get rid of the stupid purple box that it puts around the images I’m using. I’ve tried all sorts of combinations of style.
Here is what is working but adds the box around the hyperlinked image:
=CONCATENATE("<DIV><a href='","https://mysite.com/sites/mysite/web%20files/Forms/AllItems.aspx",,"' rel="nofollow"><IMG src='/sites/mysite/web%20files/"&(3-RIGHT(LEFT(CostStatus,2),1))&".png' /></a></DIV>")Maybe you can help on getting rid of that box!
Michael
March 6, 2009 at 2:06 am
Christophe
Try this in the IMG tag:
style="border:0;"March 9, 2009 at 6:49 pm
Sean
Hello All,
I mast be doing something wrong. I am simply trying to turn a row color red for announcements that are “pending” in the approval status. Any help on the calculated field would be great.
March 11, 2009 at 4:08 pm
Brinton
Worked like a charm for me – thanks for your help!
The added details in this post did the trick, it was very helpful.
March 12, 2009 at 3:04 pm
Scott
Works Great! Trying to find a way to use the filter on this column, currently when selecting the filter the end user sees the “” statement, I would prefer they see something along the lines of Low, Medium, High. Thanks
April 1, 2009 at 10:32 pm
Daily Blog Post 04/01/2009 « Murratore’s Weblog
[...] Color coding: more examples « Path to SharePoint [...]
April 9, 2009 at 8:35 am
Christophe
Hello,
Thanks for the learning. Unfortunately the following code in the calculated column actually displays the code in the list and not the intended result.
What is wrong in:
=”30,”red”,IF([Date Difference]>5,”orange”,”green”))&”;’>•”
That shows:
•
And not the bullet. Note that the conditions seems to work as the correct color is inserted in the formula.
Thanks for the help.
April 9, 2009 at 8:53 am
Christophe
Welcome to my blog…Christophe!
Did you follow all the steps described in the post, including the script in step 3? And have you checked the troubleshooting page (cf. end of the post)?
April 14, 2009 at 8:48 am
Sam
Hello,
Can you please help me to figure where i am going wrong with this syntax?
I have three images that i am trying to display based on GO/NOGO condition…. But the following code is throwing error…. Apologies i am not a programmer…
April 14, 2009 at 9:25 am
Christophe
Hello Christophe,
The step 3, that was the missing part! Mea culpa, mea maxima culpa.
Thanks a lot for the help, it’s greatly appreciated. Everything works fine now
April 15, 2009 at 1:12 am
Christophe
Sam, I think a couple quotes and slashes are missing in your formula. Try this:
April 16, 2009 at 9:53 am
Sam
Hi Christophe, i did try the code but the image is only shown for the ‘FALSE’ condition in IF statement. For all the TRUE values the column shows just the hyperlink; no images.
=”",IF([Approval Type]= “NO GO”,”nogo.gif’/>”,”return.gif’/>”)&”"
Anything missing from my side? Thanks again for your advise.
Cheerz,
Sam
April 17, 2009 at 12:30 pm
Sam
Hi Christophe, Got it. I got rid of mutiple IF’s and modified the code as below and now the indicators are working as required. Many thanks for your continued support.
April 19, 2009 at 6:40 am
Sam
Here is the code. I forgot to mention the syntax to make it visible in the blog.
April 21, 2009 at 6:18 am
Color Coding - Formula Generator « Path to SharePoint
[...] tricky part is to get the formula right in the calculated column. I have already published step by step examples to help users get started. But as soon as users’ needs slightly differ from these examples it [...]
May 11, 2009 at 11:42 am
Ian
Thanks for the posts, however I still can’t get this working, even with basic HTML. I have a calculated column with the following formula
=”•”
..yes, I’ve stuck with one colour as I can’t get anything to work, and it’s still showing up as the HTML and not what you’d expect.
I have the CEWP below the list with the relevant script put in using the source editor and we are using WSS 3.0.
Can anyone spot anything that might be amiss!!
May 12, 2009 at 10:52 am
Anonymous
Apologies, I’ve missed the sourcecode on the html. However, I’ve used the formula generator to generate a simple formula for my list which states,
however all I’m seeing is the html and not the text highlighted as expected. Is there anyway to simply test that the script is being called, as I’m getting stuck here. I’ve checked upper and lower case DIV in the script, but can’t see any other common problems I haven’t checked against.
Thanks!
May 16, 2009 at 1:55 am
Christophe
Have you checked out the troubleshooting page? See the link in the post.
May 21, 2009 at 3:20 pm
Jim Bonwell
Christophe, when I add a Group By to this list view, the tags show up instead of the color coding. Once I remove the Group By, the color coding returns.
June 4, 2009 at 4:18 pm
Eric
Hi Christophe,
I have taken your examples and they work perfectly. I love this site…Kudos to you!
I have a question because I am hitting a roadblock on a couple issues.
Question
When I use the Content Query Web Part to roll up and display Content types that contain your calculated column snippets, it does not render the same as it does in the list view even though I have the Content Editor Web part on the page with the required javascript to parse the CSS/HTML correctly.
Any suggestions on what needs to be done to make the calculated columns display correctly in the Content Query Web Part?
Any suggestions would be greatly appreciated.
Again thanks for your posts!
Eric Proshuto
June 4, 2009 at 8:19 pm
Eric
Nevermind figured that out…Just needed to set the xml date to: disable-output-escaping=”yes”
Eric
June 5, 2009 at 1:01 pm
Eric
The fix was really quite simple. The CQWP displays the HTML output escaped (just text)by default.
First I added the HTML calculated column to be retrieved by the CQWP as shown by heather Solomon’s blog, then I edited the ItemStyle.xsl file in the Syle Library on the root site to render the data.
To change the output from being escaped all you need to do is add this little snippet to the xsl select statement
xsl:value-of select=”@KPIIcon” disable-output-escaping=”yes”
Save and check in the ItemStyle.xsl file and refresh the page with the CQWP. No need to add the CEWP to the page, the html will render properly showing the desired format.
June 12, 2009 at 9:04 am
Ricardo
Hi Christophe,
Firstly, great website!
Secondly, I’m stumped – what is wrong with this statement:
=”<DIV style='font-size:12px; background-color:"&IF([OOF Type]="(1) Exam Leave", "Pink", IF([OOF Type]="(2) Vacation", "Yellow", IF([OOF Type]="(3) Business Trip", "Blue", IF([OOF Type]="(4) Study Leave", "Orange", IF([OOF Type]="(5) Maternity Leave", "Green", IF([OOF Type]="(6) Bank Holiday", "Grey", IF([OOF Type]="(7) Training", "Aqua", IF([OOF Type]="(8) Working From Home", "Red", IF([OOF Type]="(9) Sick Leave", "Purple")))))))))
I have 9 OOF Types I want to apply different background color to but it causes an error.
Any ideas??
Thanks.
June 12, 2009 at 9:27 am
Christophe
Too many nested “IF” statements in the same formula. You’ll need to split your formula across two calculated columns or simplify it.
As a reference, see my formula generator:
http://www.pathtosharepoint.com/HTMLcc/default.aspx
In your case the CHOOSE function would work better (see example 1 in the post).
June 15, 2009 at 3:24 pm
Brian
Christophe,
I’m trying to use the code in Example 5 but I don’t quite understand the use of the commas – can you explain?
=CHOOSE(FIND(“e”,[Status],1),,”black”,,,,”limegreen”,,,”gold”,”red”,,,,,”darkorange”)
Thanks
June 16, 2009 at 4:14 am
Christophe
Brian, I have added more details to examples 4 and 5, I hope it helps. I suggest that you also check out the SharePoint help on the CHOOSE function.
June 26, 2009 at 3:40 am
Case study: KPI roll-up in MOSS (Part I) « Path to SharePoint
[...] SharePoint since early 2000 using SharePoint Team Services. A couple weeks ago, Eric posted a very useful tip on my blog. I have built a case study to show how to put it to work. I sincerely hope that Eric can [...]
July 22, 2009 at 2:14 pm
Steve
Great post, this is just what I was looking for. I’m having a slight issue tweaking this, I was wondering if you could help me out. I’m not really a coder, so I think I’m just not quite wrapping my head around this. Here’s what I’m trying to do:
I’d like to change the color of a list column titled Event Name based off of another column, VIP Attendees. The Event Name is a single line of test, and VIP Attendees is a choice column (three VIPs, and None), that is a required field. I created a third column, named Color, and used your HTML generator (another great tool by the way) and modified the page using the script in a CEWP. The code I got of your generator is:
=” “&[Event Name]&”"
What I’m getting when looking at a list view is: Test in the Color column when looking at a List View of the Calendar, and the Event is in blue when looking at a calendar view. Any ideas or suggestions would be greatly appreciated.
Steve
July 22, 2009 at 4:47 pm
Steve
Scratch that, I sort of figured it out. What I’m getting now, under the Color column, is the word red, instead of the Event Name being colored red. This is my code for the second calculated column:
=”"&[Event Name]&”"
Any ideas?
July 24, 2009 at 12:12 am
Jim
Do I need to be running MOSS for this to work or is WSS ok? Being a newbie here I’m trying to determine if this will screw up my “view” list that I have prepared? Can I edit the view to add this code and make the color html work? Thanks ahead of time.
July 24, 2009 at 2:29 am
Jim
Ok, I am in need of some basic help please.
I have a list, it has a defined view. I want to add the Traffic Indicators as my Severity level as shown. I created a column Titled “Severity” which has 3 choices, High – Normal – Low. I created a column to house the icons Titled “Indicator”. I used the formula generator to create my code
=”• “&[Indicator]&”"
I created a third new column Titled “Code” (as a placeholder) and stated this is a calculated column and entered the code above.
In return my datasheet view shows the HTML as text as expected. Where do I add the additional code needed to display the icons? Sorry but I do appreciate the help here if possible. Thanks
July 24, 2009 at 4:08 pm
Tsvetelina
Hi Christophe,
I tried that and it worked fine, although I am not at all familiar with HTML scripts… Your expalanations and examples are really comprehensive. I have 1 question though… Can you export to a Spreadsheet the SP list and how will the column containing the formula for applying the formatting look like?
Thanks for your comments!
August 14, 2009 at 7:42 pm
Pritesh gandhi
Hi ,
Tremendous Work …..
It’s same as KPI
September 2, 2009 at 3:40 am
Renee Jack B. Dumanhug
Hi,
Your post was referred to me and as I read and view, it seems like this will helped me to some pressure I had in our corporate portal. I’m not a developer but I was assigned to maintain our portal using MOSS2007. However, maintaining our portal is not the only task I have right now because there are enhancement given to me.
One task I am pressured with is to have a color distinction for the Approval Status Column of our leave application system list in a calendar view. I filtered it by Organization column then by Approval Status column since they want to see a calendar view in each organization. Currently, I have calendar view in each organization which displays all application filed either Approved, Pending and Rejected. What we need is to distinguish through color coding its Approval Status like: Approved = Green, Pending = Yellow, Rejected = Red.
Please help how can I do this. I find it hard to do it in Sharepoint Designer.
Thanks.
Jack
September 2, 2009 at 1:01 pm
Christophe
Well, the above post should answer your questions… You can also try out the formula generator:
http://pathtosharepoint.wordpress.com/2009/04/21/color-coding-formula-generator/
September 4, 2009 at 9:34 am
Renee Jack B. Dumanhug
Hi Christophe,
Upon reading from one link to the other, I wasn’t able to find the steps to execute this. I tried that formula generator but honestly, I don’t know what to do next. May I know where I should place that formula? What tool should I use to do this? Should I use a Sharepoint Designer since we have it here?
I hope you can help me on this the way you were able to helped others.
Thank you in advance.
Jack
October 5, 2009 at 9:39 am
bingvillamor
Hi Christophe,
I have applied the traffic signal formatting on the column but only the formula appears. Where I did I go wrong?
Maraming Salamat! (Thank you very much in Filipino)
Bing
October 5, 2009 at 11:10 am
Christophe
Bing, follow the links in this post for more info. In particular, this should lead you to the troubleshooting page:
http://pathtosharepoint.wordpress.com/2008/11/01/troubleshooting-your-html-calculated-column/
October 15, 2009 at 7:30 pm
Jamie VanDu
HEllo there, I’m trying to figure out the syntax to have the following column be background colored when chosen. I have 5 choices and want two colors:
(5) High [to be red]
(4) Moderate High [to be red]
(3) Moderate [To be yellow]
(2) Moderate Low [To be yellow]
(1) Low [To be yellow]
ALso, what tyoe of column should I be choosing?
Thanks in advance for your help.
November 13, 2009 at 2:46 pm
Emma
This guide was brilliant, thank you. I have it working perfectly to display a bullet showing a RAG status of a project.
However, I have applied exactly the same method to display a coloured number to indicate a risk level. The only difference is that my risk number is actually a calculated value (by multiplying two other columns). The number displays as a bold 25 (so I can see html is working as intended), but is always black.
My colour column simply says =IF([Calculated Risk Level]=”25″,”red”)
I am guessing it can’t capture the number for a calculated column. Any way around this?
November 19, 2009 at 1:45 am
Christophe
Emma, what output format did you choose for your calculated column: text or number? If number, try using 25 instead of “25″ in your IF statement.
November 30, 2009 at 12:03 am
Chris
Hi Christophe, thanks for all the great tips, I implemented colour coding for Lists, Calendars and a Forms List, I found the Lists part relatively easy, I really struggled with the Forms List as you don’t seem to have any instructions for this, but I eventually figured it out, and I really struggled with the Calendar List until I realised I need ed to change the field it was using for the Text, but once I figured it out it worked great.
I also implemented the tabs too, that was too easy
Thanks again.
Chris