Apply color coding to your SharePoint lists

Color coding is very useful to highlight key items in a list. For example in an issues list, you may want to draw the users’ attention on items with high priority.

In the picture below, I have aggregated 4 different ways to do it:

This customization was entirely done through the SharePoint UI. Let’s see how.

The method

I am simply going to apply my post about using calculated columns to write HTML. For each example, we need to figure out the formula that will create the HTML string. Then the script, included in a CEWP, will make it behave as real HTML.

The formulas

In my example, I am applying color coding based on the priority of each issue: red for high, yellow for normal and green for low. Note that the formulas below were built for an issues list, formulas for other lists or libraries may be slightly different.

So here we go, please refer to the above picture to see the result:

Traffic light (actually a big bullet!):
=”<DIV style=’font-weight:bold; font-size:24px; color:”&CHOOSE(RIGHT(LEFT(Priority,2),1),”red”,”orange”,”green”)&”;’>&bull;</DIV>”

Indicator (reusing the default SharePoint KPI images):
=”<DIV><IMG src=’/_layouts/images/KPIDefault-“&(3-RIGHT(LEFT(Priority,2),1))&”.gif’ /></DIV>”

Font color:
=”<DIV style=’font-weight:bold; font-size:12px; color:”&CHOOSE(RIGHT(LEFT(Priority,2),1),”red”,”orange”,”green”)&”;’>”&Priority&”</DIV>”

Background color:
=”<DIV style=’font-size:12px; background-color:”&CHOOSE(RIGHT(LEFT(Priority,2),1),”red”,”orange”,”green”)&”;’>”&Priority&”</DIV>”

Simply paste your preferred formula in a calculated column, add the script on the Web page as explained here, and voila!

Update: check out this post for more step by step examples!

About these ads

307 thoughts on “Apply color coding to your SharePoint lists

  1. Hi! This is great. We’ve been needing something like this in the past. We’ve had to open up SharePoint Designer in order to get it to work like the way you did. The one question I had is what if you want to base the color coding off a different column? We wanted to base it off a status column (open, closed, in progress, etc.) and I’ve been unable to change your code to fit that. Any words of wisdom?

  2. Thanks Amita :-)

    In your case, instead of CHOOSE you can use IF statements. For example:
    IF( [status]=”open”, “red”, IF([status]=”in progress”, “orange”, “green”))

    • Hello,
      I am currently trying to do almost the same exact thing, except we are using a call log and I would like to be able to have a choice of Open and Closed with Open being Red and Closed being Green, is there any way I can do this

    • Christophe – I am trying to add this code, tweaking it for “approval status” but when I try to post this code:

      =”•”

      I get this SP Error:

      The formula contains a syntax error or is not supported. at Microsoft.SharePoint.Library.SPRequestInternalClass.AddField(String bstrUrl, String bstrListName, String bstrSchemaXml, Int32 grfAdd)
      at Microsoft.SharePoint.Library.SPRequest.AddField(String bstrUrl, String bstrListName, String bstrSchemaXml, Int32 grfAdd)

      Any Thoughts?

      Thank you!

    • Hello Christ,

      I am trying to do the same using this formula, but after click ok, I got an error message, could you please let me know the complete sentence for this formula?????

  3. Have you thought about applying the color coding to other sorts of lists, like a calendar? Our clients are always looking for ways to implement color coding into the calendar web part.

  4. Pingback: Applying color coding to SharePoint list : Justgeeking

  5. Amita: I am working on enlarging the scope of this method, and I hope to publish updates in the weeks to come. That’s what I already did for grouped views, based on the feedback on my initial script.
    For calendars: I have nothing ready yet. For now, you can play with the script – for example replace ((TDContent.indexOf(“<DIV”) == 0) with ((TDContent.indexOf(“<DIV”) <=12) and see what happens. Also, see Ben Bradley’s comment here:

    http://pathtosharepoint.wordpress.com/2008/09/01/using-calculated-columns-to-write-html/

  6. Ryan: what do you mean by “very large”? One way to reduce the width is to use a shorter column name, for example “!” or “::”.
    You should also be able to include the width in the formula, though I haven’t tried it myself.

  7. Thank you for this post. I’ve tried other codes provided by different posts with no luck. I actually applied your code and it’s working beautifully.

    I’m not a developer by trade so I need further assistance from you. I’m trying to apply it against the % Complete column (>=.9,”GREEN”, etc.) and I’m not sure on how to change the code to make it work. I used the indicator code but I received broken images. Can you help me with this?

    Thanks in advance!

  8. I am trying to use the IF statement above instead of Choose – not the best with code: Status is the field and WIN, LOST are choices….

    =”•”

  9. Pingback: My SharePoint scripts tested in Safari « Path to SharePoint

  10. Pingback: Should you replace Excel with SharePoint lists? (Part II) « Path to SharePoint

  11. Pingback: Progress bar + color coding « Path to SharePoint

  12. Christophe,

    For months now my boss wanted me to color code a list we maintain to highlight priority and status. Until I discovered your website, I could not get it done. You went above and beyond what anyone else has ever done in helping us overcome some pitfalls and glitches (self-inflicted). Thank you for your commitment to making SharePoint a tool we can use in a more tailored fashion.

  13. I am definitely a novice when it comes to Sharepoint development, so bear with me here. :)

    I have a calculated column in a Sharepoint List called “Days Overdue” and I am trying to create another column called “Overall Status” which compares the information in the Days Overdue column and sets it to a green or red background based upon the information present. I can get it to work for text – that’s about it.

    =IF([Days Overdue]>-1,”Red”,”Green”)

    Can someone provide some assistance? Thanks!

  14. In my examples, the color is decided by the function:
    CHOOSE(RIGHT(LEFT(Priority,2),1),”red”,”orange”,”green”)

    Simply replace it with your function: IF([Days Overdue]>-1,”Red”,”Green”)

    Just curious: how do you determine the “days overdue”?

  15. Hmm it’s still not working and this is what I have…

    =”-1,”Red”,”Green”)&”;’>”&[Days Overdue]&””

    Sample of what I see from table:
    71

    As you can see, when I view my table I am getting text instead of 71 with a background color of Red. Any other thoughts? I know my company is using an older version of Sharepoint which doesn’t have all of the bells & whistles as compared to the newer version.

    As far as your question:
    You can trick sharepoint into creating a column for today’s date which you can then compare today’s date to another date (in this case revised date). As a result, I created a column for “Today”, with single line of text and then created another calculated column “CalcToday” and set it equal to the “Today” column. Once I was done I deleted the “Today” column and voila! You have a column which is set to the current date! Then I just created the “Days Overdue” column which compares date information from certain items to be addressed/completed (a.k.a”Revised Date”) to “CalcToday”.

  16. LoL fail. Formula isn’t posting as desired…if you have e-mail Christophe I can send it to you…essentially I took the same information from your background formula and inserted the if statement and also modified the end &Priority& section for Days Overdue.

  17. Figured it out – was missing the script in the code to change information to HTML. It works perfectly. Your site and overall guidance has ben great. Thank you!!!

  18. Pingback: The “HTML calculated column”: stay tuned! « Path to SharePoint

  19. Hello

    I try to implemente indicator in my infopath form library using following formula as “My Severity” column name.

    =””

    here field name is “Form Severity” which contain High, Low, Medium values. As this field contain spaces in field name, so I have used field with square bracket as [Form Severity].

    I have also added HTML in CEWP.

    Once I implement this in any view , it is giving me #VALUE! under “My Severity Column”.

    Can anyone suggest me whats wrong I am doing?

    Disha

  20. 3-RIGHT(LEFT([My Severity],2),1))&”.gif’

    I try to post my formula 2-3 times, somehow it is trimming from the post

  21. I am trying to put a traffic lights column on a task list. I’ve copied your code to the CEWP as it is on the site. When I go to make my calculated column this is what I use

    =IF([drop test]=”Red”,”http://sharepoint…/Red.bmp”,IF([drop test]=”Green”,”http://sharepoint…/Green.bmp”,”wrong”))

    [drop test] is a choice column.

    This work but only that it shows a link to the picture and not the picture, what do I need to change so it will work with your code and display a picture

  22. Pingback: Troubleshooting your “HTML calculated column” « Path to SharePoint

  23. I have tried your example and it doesn’t quite work either. I used your CEWP code, made an issues list with a title and priority. I used your calculated column code for the calc column (=”•”)

    It works for the logic but the pictures don’t show.
    Priority Normal(2) returns this exactly:

    Priority High(1) returns this exactly:

  24. Pingback: SharePoint calendars: color coding, hover effects, etc. « Path to SharePoint

  25. Pingback: Color coding: more examples « Path to SharePoint

  26. I fall into the group of trying to use this in a custom list (I believe that’s what we created/imported) rather than an issues list. I fooled around with this for a good hour, realized we imported a spreadsheet hence the “custom list” list definition and base type. I finally setup the demo issue list to keep my sanity and confirmed I was doing it right.

    Any idea what needs to be changed to get it working in a custom list?

    Thanks, great info though!
    Brian

  27. Hi Christophe, thanks for sharing the tips.
    I had tried your method and it works fine. However, when I tried to print it out, the colour have gone. Do you know how to print with the highlighted backgroup color in it as well?
    Many thanks.

  28. Hi Christophe, I tried and it works but the problem is I don’t have a fixed size for the cell like the example given. The size of the column cell have to depend on the “Texts” that entered by my user. In this case do you know how to make it flexible?

  29. Pingback: Important References - No Programming « Sharepoint Programming

  30. Hi Christophe, I have a birthday list, which has a employee name column and current year birthday column, This current year birthday column is used to extract the birthday month and date and put it in birthday column, however, I want to highlight the particular birthday in the list on the particular birthday date. please suggest me a way to do.

  31. However, I want to highlight a birthday when birthday=[today], but I am not able to apply the if condition on the list, please suggest

  32. Again, see the link I suggested. Birthdays are just a particular case of countdown where nothing happens before or after the date, and the field is highlighted the day the date is reached.
    Browse other posts in this blog to find examples of highlighted fields.

  33. I have used these steps to create the birthday list
    Create a Custom List for your Team Roster/Key Contacts. Add columns for Name, Role, and whatever other information you are tracking.

    2. Create a Custom Column for “Current Year Birthday” column as a Date data type to display Date Only.

    3. Create a Custom Column for “Birthday” column as a Calculated data type to calculate a Text data type with the following formula:
    =TEXT([Current Year Birthday],”mm/dd”)

    4. Create a Custom Column for “Current Month” column as a Calculated data type to calculate a Date data type with the following formula:
    =DATE(YEAR([Current Year Birthday]),MONTH([Current Year Birthday]),1)

    5. Create a Custom Column for “Next Month” column as a Calculated data type to calculate a Date data type with the following formula:
    =DATE(YEAR([Current Year Birthday]),MONTH([Current Year Birthday])+1,1)

    6. Create a New View on the Custom List that displays the Title and Birthday columns and filters on the following criteria:
    [Current Month] is less than or equal to [Today] AND
    [Next Month] is greater than [Today]
    Display: Name, Birthday
    Sort: Birthday, Ascending Order

    7. Add a List View Web Part for the Custom List to a page that uses the View created in Step 6.

    however, it only show birthday for particular month, now i want to highlight the current date birthday with particular color, please suggest how the same can be achieved

  34. You can do this through SharePoint UI? Can you post some instructions?
    I am trying to flag items in a data view. For example if ‘Amount > $500′, I want to highlight it red. Or flag it in some way.

  35. Hi Christophe, first of all, I wanted to thank you for all your hard work and for giving us solutions to our SharePoint related issues. I have implemented your example for the colors, and it works beautifuly.
    There is one issue though that I am not being able to resolve. If you try to filter on a grid to see only Green bullets (for example) all you see is the HTML code “<DIV style ….". Is there anyway to see either the bullets or a friendly text? I have tried to input comments on the begining of the HTML code like:

    =" <DIV style=’font-weight:bold;……"

    But it didn’t work. Do you have any idea how to implement this?

    Thanks in Advance,
    Ralph

  36. My code that I just posted, didn’t post correctly because I had a comment tag :)

    =”B_ “&IF(Status=”Completed”, “green”, “red”)& “E_ <DIV style=’font-weight:bold; font-size:24px;….."

    where B_ is the begining of the comment tag.
    where E_ is the end of the comment tag.

    Thanks

  37. Ralf: the only friendly text I can think of is “This column cannot be filtered”… and this is possible by setting the “filterable” attribute to false.

    I have done some tests to try and display the HTML (bullet or other). I am able to do it for simple cases. I’ll try to improve it and share my findings this month.

    • Hi Christophe,
      Many thanks for your great posts. Just wondering if you ever managed to get a workable solution going for filtering on the “traffic lights”?
      steve.

  38. Thanks for your answer…I was able to do it, the problem was because I was putting the code to have the comment before the and the java-script expects to be in the position 0.

    So, all I had to do was put it after the …it doesn’t look great, but it is better than nothing. :-)

    I will send you the image by email.

    Thanks again

  39. I just implemented this solution for a client on a SharePoint task list and it is working wonderfully. The best thing about it for me is that the “traffic light” code allowed me to use colors other than red/yellow/green, which was the client’s request. Merci mille fois, Christophe, for the solution and code!

  40. Hello Christophe,

    I am trying to create a ‘Traffic Light’ status indicator for tasks using the % Complete. I have been unsuccesful at creating the script in the calculated column for this. I kept getting a Syntax error. Would you mind taking a look at my script and see what I did wrong?

  41. Thank you so much for this post. It has been super helpful. The execs definitely like a color coded pretty SharePoint dashboard over a mono-chromatic one.

  42. God you are the man! I’ve been searching for a solution to this.. thank you for being kind enough to post the solution.

  43. If you have time.. How would you code it if you want 0-3 ?

    I was thinking –

    ”CHOOSE((RIGHT(LEFT([Expected User Impact],2),1),0)”green”,”yellow”,”orange”,”red”)

    Of course it’s not working though..

  44. Hi Christophe,

    I’m not a programmer by profession, but a PM trying to set up a risk mgmt list in sharepoint. I have a field called color and I’m trying to have its color changed based on the value of a field called “Weight”. I created the color column as a calculated field and put the following formula in it:
    =IF([Weight]<=6,”GREEN”,IF([Weight]<=11,”yellow”,”red”))
    Now I’m not sure where exactly to write the web part. I opened up the list, chose the CEWP from site actions, and am confused as to what code to write in it, and also howwould the web part know which field’s color I’m trying to change?? Sorry if this sounds too dumb, but any help you can provide would be very appreciated. the above foormula dispalys the word “GREEN” or “red” appropriately, but does not show any color in the color field. Thanks so much.

  45. Kirti: you are on the right path. As you are just getting started with this, I recommend this page where the examples are more detailed:

    http://pathtosharepoint.wordpress.com/2008/12/09/color-coding-more-examples/

    For the code in the CEWP, simply follow the links in the post. It may look like a maze, this is because color coding is just one application, the scope of the method is actually much broader.
    Hope this helps. If you need more details let me know!

  46. Hi Christophe,

    Thanks for your help on this. It works (finally!) in the standard view. However, when i view it in the datasheet view, it still shows the HTML and not the color. I looked in the datasheet view and iv can see the CEWP code there, so am not sure what else is missing. Any suggestions?

    Thanks so much.

  47. This collection of posts has been invaluable. I have several “dashboards” that have benefited from this. Thank you so much Cristophe!

  48. Hi Cristophe,

    The color coding works when i use your code.
    I am trying to set up the field to show a bullet (trafic light) as well as show the name of the color next to it (since we have some color blind folks here). It does that correctly, except that if i reduce the font size to 12, it also reduces the size of the bullet. How can i change the lettering to 12 while still keep it as a big bullet. Please respond only if you have time, since i know that this is something that i may be able to find in an HTML manual as well. Thanks so much.

  49. I am trying to use the bullet point formula on SharePoint 2003 server. what happens is I get the html code instead of the colored bullet point any ideas?

    Thanks

    J

  50. Christophe –

    Excellent articles on how to do this stuff. I’ve adapted some of your work for a client and in the process discovered a number of things. First, in your KPI Indicator example above, you use an indexing of 1, 2, 3 for the indicators. I have a WSS 3.0/MOSS 2007 installation and found out that the default indexing is really 0, 1, 2. I also found lots more KPI Indicators that I discuss in my blog (http://tjdoherty.spaces.live.com/blog/cns!4FFA26922BDE54A3!405.entry). Keep up the good work and thanks for your assistance in helping me help a client.

  51. Christophe –

    Please disregard my earlier comment. Not being familiar with the formula you used for constructing the KPI Indicator (KPIDefault-”&(3-RIGHT(LEFT(Priority,2),1))&”.gif’ />”). I thought the math it was doing was 3-(2 or 1) resulting in either a 3, a 2, or a 1 index number. Now I realize that the math is 3-(the 2nd character of “Priority”, of length 1) which will result in either a 0, a 1, or a 2.

    Sorry, I did not mean to intentionally disparage your excellent work.

  52. Pingback: links for 2009-02-16 | Alpesh Nakar Blogs on SharePoint, Microsoft and that's IT

  53. HI Christophe
    I have a Status Field, like Amita, and want to display GREEN, YELLOW, RED based on the value of the status. I have tried replacing your &CHOOSE with the IF example you gave and SP thinks there is an error in the formula. Here is my formula – see anything wrong?

    =”•”
    where Goal Confidence is the status field.
    Any help would be appreciated

  54. hi,
    I tried applying the font code to a different column then the priority one but it just doesn’t seem to work. i keep getting a syntax error.

    this is the IF statement that i made

    =IF([On Time Status]=”Overdue”, “red”, IF([On Time Status]=”On time”, “orange”, “green”))

    but I’m not sure how to past it in the the original code :

    =””

    just doesn’t work. what am i doing wrong ?

  55. netta, follow the link at the end of the post, you’ll find more step by step examples showing you how to continue once you have selected the color.

    Your first formula seems technically OK, I just don’t understand why “on time” is associated with orange.

  56. Christophe,

    This is awesome stuff.

    I know too little about HTML. I tried to copy your code, but the result of the calculated column is showing the HTML rather than formating it.

    My formula:

    =””&[Serial Number]&””

    Result:

    3001302813-010-1

    Any ideas? Thanks

    Eric

  57. Christophe,

    Using the code below, I was hoping the calculated columns would produce dates. I do have Sharepoint set to the date data type.

    //=””&[Notf Date]&””

    Instead of getting 2/17/2009, I get 39861 conditionally formated correctly.

    Do you have any ideas?

    Eric

  58. I am posting this here because you are using the CHOOSE function. My question related to, I think, CHOOSE. While we create these colorful fields I have found that you can not add “SUM” these values, because it is not just a number, it is am entire string. Looking at my string it ends the same way. my integer and two closing DIV tags. Can the CHOOSE function or any function allow me to grab just my integer so I can sum multiple calculated values?

    Example:
    [DIV ....][DIV...]0.234[/DIV][/DIV] can I grab the 0.234 from the string? It may not be 3 decimal places, but will always be between the DIVs

  59. Have you tried using either MID or a combination of LEFT and RIGHT to select just the digit and then convert it to an integer before using SUM? Excel has a VALUE function that convert a number string to an actual number value. I think I was able to use it in a calculated column. Just be prepared, your formula may get very long. I suggest creating columns for the intermediate steps. And then once you get it all working, you can combine the formulas in all the columns.

  60. Looks complicated. *Anonymous* is correct, use a combination of LEFT and RIGHT, and also FIND and SEARCH. On top of this don’t forget VALUE to convert your 0.234 from string to number…

    I assume 0.234 is pulled from somewhere else, so why not just use the original column?

  61. Thanks for the great code. One quick question. How can I center the text within the display column (I don’t want it left aligned).

  62. I’m using your script to highlight the title of a calendar item based up a selected category. When I change my calendar month view title to use my new highlighted title it breaks the <a href tags so there is no longer a link to that calendar item. Although the highlighting works great.

  63. Pingback: Modificare modelli in Sharepoint Web Service 3 | hilpers

  64. Hi,
    I am working on a document library and I wish to add indicators and colours as shown in your post. When I convert to an XLST data view to apply conditional formatting, the “edit in datasheet” dissapears from the actions menu.
    Is there a way to resolve this issue or maybe another approach using the document library?

    Thanks
    Regards

    Chris

  65. Chris, let me clarify a few points.
    First, this post is NOT about the XSLT data view. We are talking about the standard SharePoint lists here.
    Second, it is normal that you lose the “edit in datasheet” when you use the XSLT data view. The “edit in datasheet” is not HTML, it is a Microsoft control (which btw only works in IE).

    Note that if you use my method, you’ll see that it doesn’t apply to “edit in datasheet” (for the same above mentioned reason).

  66. So far so good – I managed to get my list working with the ‘Traffic Light’ style of indicators. Now I am trying the ‘Indicator’ style and can’t find the KPI default images that you refer to…

  67. At first: Thanks for your great work!
    I try to paste the formula but I keep getting Syntax errors. I suspect it has something to do with the fact I have to use semicolons as separators, in stead of comma. I experienced this before with more simple formulas. I believe this has something to do with using non-us versions of WSS (although my SP language is english).

    When I replace the comma’s with semicolons I still get the syntax error.
    Do you know how I should adapt the formula, so that it works?

  68. Do not paste the formula, type it. My blog editor formats quotes in a way that is not suitable for SharePoint.
    This was one of my first posts. In later posts I have included friendly “copy to clipboard” options (see for example the update at the end of the post).

  69. Sadalit: I did a quick check, and some colors are not correctly rendered on that page (e.g. puce and aliceblue).
    My usual reference is w3schools. They have charts that help match text color and background. However, it seems that their list is not complete (puce is missing for example).

  70. Hello Christophe,

    When I try to apply the color coding to a calculated column supposed to display a date, I get as a result the date numeric value and not the date formatted value.

    The calculated column has the following settings:
    Data type returned: Date and Time
    Date and Time Format: Date Only

    The formula used is:
    //=”30,”red”,IF([Date Difference]>5,”orange”,”green”))&”;’>”&[Planned Date]&””

    [Planned Date] has the value “31/03/2009″
    [Date Difference] has the value of “0”

    The calculated column shows “39903” instead of “31/03/2009″ although the color is green (that part works fine).

    Is there something that could be done or are dates not an option here?
    Thanks for the help.

    Christophe

  71. I have implemented this in many places…THANK YOU. Now I am doing one where they need me to on the VIEW “Group By” the Indicator column itself. When I Group By the indicator field I only see the HTML code, but if you expand the grouping I see the proper indicator lights underneath. So I assume I need to modify the Java Script??

  72. Pingback: Color Coding - Formula Generator « Path to SharePoint

  73. I still do not understand how to get it to render properly when the Indicator field is a “Grouped By” field in a view. It works fine if my indicator field is NOT the Grouped By field…but doesn’t render properly if I make it the Grouped By field.

  74. Hello all!
    First of all, congratulations for this site!!!
    I am trying to apply this formula to my Sharepoint site (v2):
    =””&Priority&””
    but I have the following message each time I try to validate:
    “The formula contains a syntax error or is not supported.”
    Everything works well for the v3 version (I have another site on this version and it works!).
    Any idea?
    Thank you very much for your help,
    Marcia

    • This is not directly related with this topic but since it touches a similar subject…
      I’m experiencing a problem with formulas, I have 2 farms running. On one of them I used the following formula “=IF([OSInstalled],0.75,0)” which works fine. I tried using the same formula on the other farm and I get the syntax error message.

      I’ve tried switching the “,” to “;” and I still have the same problem.

      The only difference between these farms is that 1 is x86 and the other is x64.

      Any ideas?

      Thanks

  75. I can’t seem to figure out a way to remove commas from numeric values in SharePoint 2007 lists. ?

  76. I would like to return the value of the Priority Column into the Font Color column, but remove the (1) from the value returned, so my colored text does NOT display (1) Low, but rather just Low

    Using MID, the syntax for this “should” be: =””&PullFrom&””

    this is accepted but returns #VALUE

    Should I be using something different?

  77. This is great! Works perfectly! Does anyone know of a way to look up a column value from a previous version of a list item? Essentially, want to show previous priority of an item so you can see previous and current states. Thanks!

    ??

  78. I can get both the new code and the old code to work perfectly in my list, but when I try to bring my view from my list to a Webpart on another page it does not work. I have verified that I have used the same code on both pages, any ideas?

    • Ken, make sure you put the CEWP with the code after/under the Web Part on your other page. Apart from this, I see no reason why it wouldn’t work…except if you have some very specific customization on that other page that conflicts with my code.

  79. That was my first concern, so I pulled everything out and left just the Announcement List and the CEWP on the page, same issue.

    I also started a new page and but the issue continues.

  80. Christophe,

    Looking more into it I think I have narrowed something down.
    My Test Page contains the Following:
    Annoucements Web Part(List with color)
    Calendar Web Part(with Color)
    CEWP (TextToHtml)
    CEWP (Mini Calendar)

    My Color on the Calendar works both on the Calendar Page and in the Webpart
    My Annoucements work fine on the annoucement Page but displays
    background: test announcement
    Instead of the color.

    I can remove the Annoucment Webpart with no Issues, I can remove the Calendar webpart, and the mini calendar and the issue remains.

  81. Pingback: HTML Calculated Column – Updated script « Path to SharePoint

  82. Pingback: HTML Calculated Column – Updated script v2.1 « Path to SharePoint

  83. Hi friends,

    I am trying to use Traffic Light color code,
    Pls let me know which code to be added to CEWP

  84. Hi, would like to find out.
    How to auto update the library information?
    I have a Task Library, with a calculated column on Tasks’ Status with colour applied (using the colour coding method). The formula I used in the calculated column is to compare System Date with the Plan Date column. Actually it works pretty well. The only issue is the Task’s Status/colour will not get auto update if System Date is over than Plan Date.
    Is there a way to refresh the Task records in the Task Library?

    Thanks
    Demi

  85. I noticed that when I used Bullets with a Task List % Complete that the bullets were slightly off set from the rest of the items. I solved it with a simple CSS addition of:

    line-height:50%;

    =”=0.8,”Green”,
    IF([% Complete]>=0.6,”Yellow”,
    IF([% Complete]•”

    Great stuff! Thanks for all the help and code support!!

  86. Pingback: An example combining Gantt view and color coding « Path to SharePoint

  87. Hi,

    I am facing a problem that the filter on the view for the color coded column still show the tag. any idea if i can disable it in some way?

    Regards,
    sahil

  88. Is it possible to perform the conditional formatting using a formula to is if the date in a column is less then or greater then todays date. Having are hard time getting around this since i can not use =Today in a calculated column

  89. We recently applied SP2 for MOSS. Since then, my color coded columns are not showing the colors. If I go into Edit Mode, then it is working again, but as soon as I exit Edit Mode, the background colors disappear. Any idea what is causing this and is there a work-around?

    • What can I do if I do not access to CEWP??? Any other way to make it behave as real HTML??? :)

  90. Thanks for this great site!

    I’m a complete newbie to SharePoint, but I’d like to use color coding on a task list I’m developing.

    Do the examples above apply to SharePoint Portal Services 2003?

    Thanks again!

  91. Hello!

    I have created a custom list using Import spreadsheet. Im my original excel file I have column “A” color coded blue, column “B” yellow etc. My question is how to colorcode the different columns, not the rows. EG I want column called “Title” be blue, Column “Name” be yellow etc.
    I only find how to color code a row, not how to colorcode a column.

    I hope you understand my question and can help me out.
    Many thanks in advantage
    Staffan

    • The method allows you to color code one cell in a row. So if you simply apply the same color to all items, you should get your color coded column. Did I miss something?

  92. I am having one minor quirk when i use the traffic light color coding. For some reason the bullet itself is wanting to push down in the cell causing the row to be off on spacing. I noticed that all of your data on the row for each of the calculated columns lines up exactly. What am i missing that is causing my bullet to be further down in the cell causing my spacing issues?

    As always, great site and thanks for any and all assistance.

    Ex. data data data
    bullett

    • Are you calling me a cheater? Well, you’re right…by default a bullet will not be centered in the cell. So for the screenshot I applied some additional styles to the DIV element (a bottom margin or something like that).

  93. Pingback: The HTML Calculated Column, one year later « Path to SharePoint

  94. Hi Christophe,

    This is really amazing. I can get everything to work properly except the Indicators. I seem to be misunderstanding the process. Can you please explain it a bit more. Thank you so much for everything – Heidi

  95. Hello again, I can make the indicators work for the three different priorities. However, I need a fourth called Archived and that won’t work even though I made a shape for it that followed the naming convention for MOSS. We’re on WSS 3. Thanks again. This is just what we need!

  96. Sorry to keep posting, but my problen was on my part. It was in the naming of the GIF files. One didn’t pass properly.

  97. Hi Christophe,

    This is an awesome tutorial. It’s very well written, and easy to follow. I have a different question that relates to this problem (color coding!)

    Essentially, I’m in the process of building a sharepoint site to deal with emergencies we end up having on my military installation. One of the things I’ve created is a digital version of the checklists we use. Currently, the status of a checklist item is a choice box (Unset, NA, In-progress, On-going, or Completed). The viewable side is a Standard View of the checklist, and it’s edited by a Data Sheet View.

    The boss wants the word “Completed” on the Standard View side to turn green when people using the Data sheet View switch it from “Unset” to “Completed.” Is there any way to accomplish this via CEWP or the XML version? I know your tutorial here calls for a Calculated column to change colors, but I’d like to try and avoid adding (yet another) column if at all possible.

    I don’t care if it turns the entire row’s background green, or just the word “Completed.” I can explain away any weirdness provided it changes color. Unfortunately, being that it’s government computers on a Sharepoint server owned by a higher headquarters, I can’t go in and do it the easy way with Sharepoint Designer. Any help you could give me would be greatly appreciated. Thanks!

    • Jonathan: if you can’t use the DVWP and its conditional formatting, creating an additional calculated column seems to be the simplest approach.
      You could also write a script that will search for the word “completed” on the page and turn the background green.

  98. I am looking to do some of the field coding using calculated fields utilizing preexisting images included with sharepoint. Other than the KPI icons is there a way to view all the images included in the _layouts directory since DIRECTOR LISTING IS DENIED.

  99. One more question. Using this color idea is there a way to edit the form to show the same color as the calculated fields. For example, when i create a new entry for the list can the drop down field for priority show the same color or image as the calculated field in the view?

    As always thanks for the help.
    theerdman

    • Sure, you could do this, but it would be another customization, separate from the calculated column thing (the content of the calculated column is only created after you send the form).

  100. Wonderful information! I was wondering how would I use the ISERROR function with this. I am using this formula to capture if the projects have been baselined to show the overall health. However, if there is no baseline I would like the display to show “No Baseline”, or if I type nothing in for the Priority, I would like for it to display “No Baseline”, but definitely not the #Value erro. I hope this makes sense.

  101. Going back to one of the original posts using the IF statement, I am trying to change the background based on the value of a column called ‘RPN’
    If the value is greater than 40 it should be red
    If the value is greater than 20 is should be orange
    Else it should be green. Here is the code that I have tried;

    =”40,”red”,IF([RPN]>20,”orange”,”green”))’>”RPN&””

    This is based on;

    =IF(RPN>40,”red”,IF(RPN>20,”orange”,”green”))

    which is successful, but when I try the HTML version I keep getting syntax errors but cant figure out why.

    Any help would be greatly appreciated.

  102. The last comment did not have the proper code, here is what it should have been;

    =”40,”red”,IF([RPN]>20,”orange”,”green”))’>”RPN&””

  103. Dear Sir,
    I’m currently studying WSS 3.0 and I wanted to have a calculated column that will show a green traffic light if a column called COMMENCEMENT is ‘Y’. Below is what I have:

    =”•”

    When I create the calculated column, it shows a syntax error. Can you help?

  104. Hi – i’ve applied the formula to traffic light status, and got nice coloured bullets appearing on my list. However, this only seems to work on the front page of hte sharepoint site. If i click into the different list views then only the html code displays, rather than the nice coloured bullets. Is there a way of ensuring the html conversion reaches the different list views?
    Thanks

    • Rich: the script is linked to a page, not to a list. To make it work with different views, you need to add the script to each view.
      A good practice is to add the script before you create additional views. This way, you can reuse the first view with the script to build the others.

      • I have a similar issue as Rich. I’ve created an “In/Out” log and use the traffic lights script. I created several views for each team with no problem – the traffic light works fine. However when I create a “Personal View” for just myself I have the same issue as Rich – I only see the HTML. I tried adding the Text to HTML web part to the Personal view but with no success. Any thoughts?

  105. My create view is gona now, when i get it back by creating a new view but It’s all messed up. I get multiple lists with the same content when I create another view or list disaapears when I pick a certain view.

    Anyway it gets REALLY messed up when you use this in the listpage

    But great tip ofcourse

  106. Good day – I have a list that reflects dates of when folks post 4 different forms. Basically – the column headings are the names of the forms, to the left are the offices for which the forms were submitted. I’ve looked but didn’t see this post – but could have missed it. I’d like to set this list up where if a date in any of the 4 columns is older than 6 months – it is highlighted in red. Any info would be helpful. Thanks much.

    • From the View drop down I choose “Create View”. I then “Start From Existing View”. From the “Create View” page I give the view a name, click the “Create a Personal View” radio button, select filter criteria (which is to filter on the Name column and enter my name). The view comes up like I want – with just my entry but the “Traffic Light” only has the HTML showing.

      What I want is for each staff member to create a view with just the entry for their name on the list.

      I’ve tried adding the TextToHtml web part on this view but am unable to do so. The Traffice Light HTML I use is this:

      =”•”

  107. Christophe, is there any way to apply colour coding in preview pane view to the column that is on the left (the one you hover over to display the preview). Can apply colours to any column in the right pane no problem.

  108. Hi Christophe,

    Can u please take a look at my IF funtion is not working.

    =”IF(“&[Availability Status]&”=”&”Available”&”, “&”Yes”&”)”

    This will return just string,

    IF(Available=Available, Yes)

    Do you think this problem occurs from my Sharepoint Services 3.0 itself?

    I dont know why but it seems all the functions aren’t working.

    Regards,
    Fayadh

  109. Hi Christophe,
    Great work. Thanks for sharing this.
    For some reason when I try this my column just shows the code and not the result.
    Canada

    Any ideas,
    Thanks,
    Jay

  110. Pingback: ÁghyBlog » Listatrükkök

  111. Pingback: ÁghyBlog » links for 2009-06-18

  112. I have a related question. I have a dashboard that I want to use conditional formating on.
    I want to compare 2 columns(fields) from 2 different web parts, if the data is ” to eahc other, then turn one of the coulmns “RED’.
    Thanks.

  113. Hi,

    Thanks for this example. I have been able to implement it in my site. Now, I want to export the list to excel. However, the nice colors are lost when I export to excel. Is there anyway to preserve the formatting so that it can be used as a “portable” report?

    Thanks.

  114. Hello
    i have a queation. how did he made the gantt-diagram , so that you can see the month and not a weekly view? i have no Sharepoint Designer, so is it possible to change the view anyway with Sharepoint

    Thank you for you help

  115. I have one question regarding the color coding on the sharepoint list. Has the issue where the header does not render the html coding when you try to filter or sort the list been resolved? I love being able to use all of the graphical information but when users are trying to do the sort all they see is garbled mess of HTML code.

  116. Hi Christophe

    For some reason filter menus, groupings and data-sheet edit/view, just shows the code and not the result. Am i missing something?
    Thanks
    Avi

    • Avi, these are the limitations of the currrently published version. There are ways to make it work in filters and grouping. The datasheet view however won’t work, simply because it is a proprietary Microsoft format, not HTML.

  117. My status columns values are:
    (1) Pending
    (2) Passed
    (3) Failed

    I want Pending to be yellow, Passed to be green, and Failed to be red.

    I have this calculated column in place:
    – Indicator (reusing the default SharePoint KPI images):
    =””

    Which produces Pending = yellow, Passed = red, Failed = green. !!

    I have tried everything to get the colors to “flip” but nothing works. Can you help?

    • Sorry, the code stripped out of my above vomment. It was the default “Indicator” code shown above.

      =img style=’float:left;’ src=’/sites/Workspaces/images/kpidefault-“&(3-RIGHT(LEFT(Status,2),1))&”.gif’/>

  118. I’m not sure there is a fix for this. I discovered yesterday that my formula isn’t working as expected. While it does compare todays date to another column date, it only seems to do it if I edit my row and save it. I’ve tried other methods to compare against todays date but it seems Sharepoint does not like the Excel 4.0 formula =NOW(). Any suggestions? In the included formula CalcToday is a calculated column in my list that is producing todays date. The goal here is to produce a colored diamond for each row in my list based on the value in other columns or the current date. It works fine but requires I update the row for the coloring to change. I want the colors to change without my having to update the row.

    IF([Func check out]=”Yes”,””
    ,”CalcToday,”aqua”
    ,IF([Est Cmplt Dt]=”Unknown”,”orange”
    ,IF(CalcToday>DATEVALUE([Est Cmplt Dt]),”red”
    ,”green”))))&”;’>♦”)

    For example, on 6/2 Start Date was = 6/1 but the diamond was still aqua until I clicked on edit then save without making any changes. It should have been green.

    • Im assuming your date is using the Today function in some manner. Therfore you have created the false column, entered your calculation for the calculated field and removed the Today column.

      The only way i have found to update the moving target such as a date to ensure that the calculations are refreshed and your color changes do happen is to create the Today column daily and delete it. This causes all the rows to recalc without having to edit each one individually. If you come up with an automated way of doing this i would be greatly interested.

      Thanks,
      RD

      • RD, if you’ve read my articles you know that I am not a fan of the Today trick. That said, and to answer your question, have you considered using Web services? You could create a console application or a script for that.

      • Thanks to both of you. Yes, as it turns out I am using the Useless today trick. I’ve read much on the countdown Christophe asked me to search but in the end, I believe it can not be done. My mistake was assuming the code would always use the current date for it’s comparison. In fact, it is using the current date from the last time I edited the row.

        Nuts!

        BTW, sorry about my previous post. There was HTML in my formula and naturally that didn’t display in your browser.

  119. This help me a lot is just what I was looking for and I’m so siccess thaks to you..!!

    Thanks a lot !!!!

  120. I have sucessfully added your color examples to a custom list uisng sharepoint 2007. The problem I am having I when I view the custom list item, it shows the calculated list item. Is there a way to hide this calculated column in a view?

  121. Still looking for help with this problem! Anyone got any suggestions?

    I have sucessfully added your color examples to a custom list uisng sharepoint 2007. The problem I am having is when I view the custom list item, it shows the calculated list item. Is there a way to hide this calculated column from the view?

  122. Pingback: ใน SharePoint List จะสร้างแถบสี หรือใส่ สัญลักษณ์ให้กับ Column ของข้อมูลที่สนใจได้อย่างไร « Vipnetty's Blog

  123. Guys, help needed.
    With this formula, every outcome is colored green, and that`s wrong. Why doesn`t these formulas work with > or =”9″;”Red”;IF(([Riska līmenis])=”8″;”Yellow”;IF(([Riska līmenis])=”6″;”Yellow”;IF(([Riska līmenis])=”5″;”Yellow”;IF(([Riska līmenis])=”4″;”Yellow”;IF(([Riska līmenis]) “&([Riska līmenis])&”

  124. I am a novice and I guess I need more help to understand the whole thing. I have a column name “CI” which is an user input date field. I want to color code this field based on a calculated result. I have created a new column call “CalcDate” which is set to 10/31/2010. What I want is if “CI” is smaller than “CalcDate”, then the font in “CI” will turn red, otherwise green. Is this doable?

  125. Hi all,

    I used the script for Background color: Font color but not able to see the proper result. it’s showing the text only as below.

    (1) High

    Please let me know If I am missing something somewhere.

    Regards
    Deeptyranjan

    • Hello everybody,

      I have forgotten to reply that I got succeeded to sort it out.
      I was making the mistake to put the script (script to change the formula to HTML format) at the top of the page. rather it should be at the end of the page.

      Note: Try by putting the script within a content editor web part and put the web part at the bottom of the page.

      Regards
      Deeptyranjan

  126. This works great on flat views but not my grouped one.. do I need to add something about ‘childnodes’?

  127. oops – I see the documentation. thanks.

    i’ll try figure out the issues I’m having on my own.

    thanks again

  128. i tried to add colored indicators, but still get a syntax error.

    =”<DIV style=’font-weight:bold; font-size:24px; color:”IF=([Days Open]14,”red”,”orange”))&”;’>•”

  129. This has been very helpful so far, but I am trying to take it a little and hopefully you can help me. I implemented background color column based on the 3 status’.
    Now I trying to format the background of a DIFFERENT colomn in the same list that is a dropdown choice of about 30 or so selections. I only want 2 of those selections to show a colored background. Can you provide the syntax to do so?
    Cheers

  130. Christophe – I tried this today to color status in a alist and ti worked briliiantly. THANKS!!! But once this worked I tried to export my list to excel adn all it exported was the formula and not the colos – is there anyway to do that?

    Thanks
    Anu

  131. Is there a way to calculate an overall health using indicators built in sharepoint and Project Server. I have created a Issue indicator in SharePoint for the that gives one health color for all the issues in a project workspace. In project server I have calculated a health indicator for SPI and CPI. I would now like to calucate the overall health which should include Issue Health, CPI Health, and SPI health. How can I accomplish this if the data for Issues is in SharePoint and the data for CPI and SPI are in Server.

  132. Christophe,

    Simple Priority fields: =”•”

    The output i am getting is ‘No’? :S Also if i use the CHOOSE function it will only output if numerics are used i.e 1,2 not “High”,”Low”.

    Please advise?

  133. Pingback: Jose M. Tamez | Text to HTML

  134. Chris,

    In an eariler version of this article (posted on SPC), you used your own icons and there were 4 ‘IF’ statements… I would like to use my own icons, instead of the KPI defaults. I have worked through all the ohter examples and made them work, cna you help wiht the ‘user your own icons/gif/jpg version?

    Thanks!

    Rick

    • Definitely an option, but “most convenient” is subjective. The solution presented here can be implemented from scratch by an end user, as it doesn’t require any kind of server side install. It also offers more flexibility in the rendering (see the calendar tutorial for example).

  135. i added a new column name MilestoneStatus as calculated column data type, with this formula

    =”<DIV style='background-color:"&IF(MilestoneDate”&MilestoneDate&””

    but the return value gives me a number, it should be MilestoneDate. How can i transform it to date or can i use some Format functions? pls Advise.

    • got the answer YEar(MilestoneDue) & “/” & Month(MilestoneDue). Thanks alot for the owner of this site

  136. as i apply to other machines with MOSS 2007, why is it that it is not displaying the background color, but in my first machine its working…

  137. How would this be applied to a list column with only two options? (yes, no) I keep getting syntax errors. Here is one of the options I tried

    =”•”

  138. Ok, massive failure. I’m getting the The formula contains a syntax error or is not supported.

    I moved the web part to the bottom, but I seriously think I am misreading the instructions and simply doing something completely stupid.

    I have a column that is a choice column with a drop down selection for the options Not Started, On Plan, At Risk, Mitigation Required, Complete.

    So, now I’m creating a calculated column “Status” and using the forumla above with the embedded IF statements pointing to the choice column.

      • Well, i did get rid of the error and I’m getting closer, but now it just displays the words, not the bullets or the color. I know I am doing something wrong.. however, if I put the =”•” in the calculated column formula, it errors out, I do have it in the CEWP, So, in the column I have: =IF( [Budget]=”Not Started”, “black”, IF([Budget]=”At Risk”, “gold”, IF([Budget]=”On Plan”, “limegreen”, If([Budget]=”Mitigation Required”, “red”, “blue”))

        and in the CEWP I have: both parts.

        This results in a semi working piece because the column displays the words “black” “limegreen”, etc. Because I am new to all of this, I know I’m just missing something.

  139. I want to highlight the row in task list if the task is overdue
    what will be the formula in calculated column

  140. Hello Christophe, in a related scenario, back in 2009 you did an article on Ben Schlaepfer’s Trend Reporting. That article shows a color gradient. I’m unable to locate the scripting for that gradient column — can you help me? Very grateful for your assistance.

  141. Firstly, thank you so much for your solution. It is awesome and works fine! Great blog!

    Well, I have used the trick exposed here with 4 priorities. It worked fine. I have also succeed replacing the bullet by a Wingding. Next step would be associate the following formula to show a different Wingding for each color/priority:

    =”‘☠”

    Would you help me with that?

    Thanks so far!
    You’ve done a lot!

  142. Hi there, i seem to be having a problem with this piece of code, i followed your steps however all i see is still the text and not the HTML piece. I am using Sharepoint 2010 Foundation. Could you help me with where i might be wrong

  143. Pingback: SharePoint 2007: Priority/Progress Bar using color coding within a SharePoint List

  144. Hi Christophe.

    I had some trouble with your traffic light/bullet solution when the item value is blank or not in the form “(#) Desc”.

    I resolved it by adding some particularly ugly (but effective) error checking:
    =IF(ISERR(RIGHT(LEFT([Priority],2),1)-1),””,”<DIV style=’font-weight:bold; font-size:24px; color:”&CHOOSE(RIGHT(LEFT([Priority],2),1),”red”,”orange”,”green”)&”;’>&bull;</DIV>”)

  145. Hi Christophe: As you said there are ways to make work in filter and grouping, Can you please let me know the ways? I am having the filtering issue(give the data like )

    Thanks in advance!!!
    Lovekush Kumar

  146. Pingback: A Program Management Office…in a Team Site « Ellen's Digital Workplace

  147. hi Christophe,
    actually i have implemented the above steps but the problem is that i am only getting the name of color but not the actual color light….please specify how to implement in the content editor part…as after implementing the content editor par i m only getting a black dot and not the actual traffic light as shown in picture..kindly help
    ,

  148. I added this code to the default view on a list and it worked beautifully. However, when I added a second wider view, which has a horizontal scroll bar, the icons did not appear. On editing the page, it looks like the wider view uses an entirely different page, because the webpart for the icons was not there. Once I copied the webpart to this page, it worked fine.

    • can somebody tell how to implement this thing in content editor web part…i m posting the script in HTML option and all i m getting is a black dot….i am implementing this in custom list.

  149. Thanks, the traffic light solutions you posted worked a treat and the explanation was very good.

  150. This code works great. However, I need to modify it slightly. First, I want to be able to change the text w/o it changing every cell in that column. Possible? I just want the background color.

    Second, I need to calculate days from a date for another column. I’m not sure about how to do the IF statements for dates (say, if 30 days from TODAY it is yellow background color). Again, I’ll need to add the date to the cell.

    Maybe none of this is possible?

    • All of this is possible, but not necessarily easy. Check out my other posts for more examples. For dates, search for “countdown” in my blog.

  151. Pingback: Using calculated columns to add color coding to your SharePoint lists | sarahlhaase

  152. if I have multiple images to choose from for auto-populating via a percentage range in another column, what should the code look like? All four images are saved in a folder on sharepoint. Example:

    90% = red

    images are saved as:

    bullet-green_gif
    bullet-yellow_gif
    bullet-orange_png
    bullet-red_gif

    • if I have multiple images to choose from for auto-populating via a percentage range in another column, what should the code look like? All four images are saved in a folder on sharepoint. Example:

      70 and 80 and 90% = red

      images are saved as:

      bullet-green_gif bullet-yellow_gif bullet-orange_png bullet-red_gif

  153. Great idea, thank you. I have successfully implemented this on several Document Libraries using columns created on the libray, however if I try to setup the columns at the site level I get an error that the column contains a data type that cannot be calculated in formulas.

    Additionally when you set up the columns on the library they are listed as “Calculated (calculated from another column)” but when set up at the site level they show as just “Calculated”. You thoughts would be much appreciated.

    • The tool looks interesting, however you can’t really say that no code is required. Compared to the solution presented here, the tool requires a server side install, and apparently it doesn’t support SP 2007.

  154. Thank you for this! Looking all over for a relatively easy solution and this fit the bill. The additional links to explain step-by-step were especially helpful as I’m not a master SP programmer.

  155. Hi,

    Nice Article Chirs! trying to implement PriorityIndicator Field based on Priority field in SP List. using the following formula

    [="•"]

    I have also added HTML in CEWP.

    Once I implementing this in any view , it is giving me #VALUE! under “PriorityIndicator” Field.

    Please suggest me whats wrong I am doing?

    Raj

  156. I am trying this with SharePoint online and it does not work. I can get this to work with SharePoint 2010 onprem solution. Do you have a solution that works on SP2013 online?

    Please send me an email if have details on how to get this working in SP2013.

  157. Can anyone help, I am just learning sharePoint and it seams i need a crash course on coding. I am trying to change the background color to “red” based on a sum. the field name is [total average] if it is less then 100 i would like the background to be red if its between 1000 and 100 would like the background to be yellow.

  158. Hi, excelent blog!!! It helped me a lot!
    Does anyone know if it is possible to set more than 3 color codes for Indicator option? I was able to do that for the traffic light, but not sure how it works for the Indicator…

  159. I have this code which makes my SH list extremely slow. Any ideas how to change that so the list loads faster:

    var x = document.getElementsByTagName(“DIV”) // find all of the TDs
    var i=0;
    for (i=0;i<x.length;i++)
    {

    if (x[i].innerHTML=="0")

    //find the data to use to determine the color
    {
    x[i].style.backgroundColor='#FF6666'; // set the background color as red

    }

    if (x[i].innerHTML=="1" || x[i].innerHTML=="2" || x[i].innerHTML=="3" || x[i].innerHTML=="4" || x[i].innerHTML=="5" ||

    x[i].innerHTML=="6" || x[i].innerHTML=="7" || x[i].innerHTML=="8")

    //find the data to use to determine the color
    {
    x[i].style.backgroundColor='FF9900'; // set the background color as orange
    }

    if (x[i].innerHTML=="9" || x[i].innerHTML=="10" || x[i].innerHTML=="11" || x[i].innerHTML=="12" || x[i].innerHTML=="13" ||

    x[i].innerHTML=="14" || x[i].innerHTML=="15" || x[i].innerHTML=="16")

    //find the data to use to determine the color
    {
    x[i].style.backgroundColor='#FFFF66'; // set the background color as yellow
    }

    if (x[i].innerHTML=="17" || x[i].innerHTML=="18" || x[i].innerHTML=="19" || x[i].innerHTML=="20" || x[i].innerHTML=="21" || x[i].innerHTML=="22" ||

    x[i].innerHTML=="23" || x[i].innerHTML=="24" || x[i].innerHTML=="25" || x[i].innerHTML=="26" || x[i].innerHTML=="27" ||

    x[i].innerHTML=="28" || x[i].innerHTML=="29" || x[i].innerHTML=="30" || x[i].innerHTML=="31" || x[i].innerHTML=="32" ||

    x[i].innerHTML=="33" || x[i].innerHTML=="34" || x[i].innerHTML=="35" || x[i].innerHTML=="36" || x[i].innerHTML=="37" ||

    x[i].innerHTML=="38" || x[i].innerHTML=="39" || x[i].innerHTML=="40")

    //find the data to use to determine the color
    {
    x[i].style.backgroundColor='#66FF66'; // set the background color as green
    }

    }

  160. Thank you, I have recently been looking for info about this topic for ages and
    yours is the best I’ve came upon till now. However, what about the conclusion? Are you sure in regards to the supply?

  161. Has anyone been able to make this work for a grouped list that is not expanded by default? This has been so very useful and would like to show it this way too. Thanks!

      • I downloaded the TextToHTML-v2.1.1.txt but still can’t get it to work on the grouping for SharePoint 2010. Is there another version for 2010? That one only indicates SP 2007.

  162. Hi Christophe,

    Your code works great.Thanks a lot :)
    Please let me know where can I find the updated code for SharePoint 2010.
    Also let me know how to filter with the above code as it is showing HTML content while filtering and I cannot implement this way if there is no filtering option.Please help.
    Thanks in advance.

    • There are no easy ways to deal with filtering… For the latest updates check out the SharePoint User Toolkit, and my articles from 2010 and 2012.

  163. I am not able to display colour coding icon when I group the data in a List. Could u please help me out.

  164. Using sharepoint 2010, i’m trying to locate some images to use as you described. Can’t find the images, and even if putting my own images for this purpose, i don’t know how to find out what is the path for them. Can you help me out here?
    I also tried the bullets solution, which is great, but the bullets are coming below the line of the actual text, and it is a bit confusing.

  165. Pingback: Using Client-side Rendering to color-code list cells in SharePoint 2013 | On Point

  166. My traffic light is center aligned vertically but sharepoint list has everything top aligned. How do I top align vertically my traffic lights?

  167. Pingback: Color coding in SharePoint lists using calculated columns. | Junaid Khan

  168. Hi Christophe, thank you so much for your work. I need some help on a slight variation.

    I need to use the KPI icons, since a team member is color blind. I have a column titled “Status”, with three options, which I would like to color/shape-code as follows:

    Progressing (green circle)
    Issues (yellow triangle)
    Stalled (red diamond)

    Using the formulas here, I have to reverse the data entry order and I have to include a number schematic, i.e. (1) (2) and (3) to get the right color/shapes attached to each entry.

    Is there a way to keep my ordering and match the colors/shapes to my order? (i.e. the first in the list is green circle instead of red diamond)

    Also, is there a way to lookup the KPI icons using text only so I do not have to include the numbering? I looked at your followup blog and can replicate the changing of font colors using the “e” trick, but I’m wondering if this can also be done when changing the KPI icons?

    Thanks!

  169. Great help, works as supposed on sp2010, but I can’t make it work on sp2013. The html code is generated via calculated field, but the script part does not run. Is there some limitation on sp2013. I tried CEWP, HTML form webpart and script editor webpart.

  170. Hi, I am a complete novice looking for help!
    I have a calculated column for growth, shown as a %… I am looking to show any nagative as red (i.e. -58%)

    The calculated column I have is ‘Book Growth’ with the following calculation:
    =SUM([Book, 2014]-[Book, 2013])/[Book, 2013]

    Where do I start with this?

    Thanks in advace for your help!
    Helen

  171. Pingback: Apply color coding (KPI Status Indicators) to your SharePoint lists | SharePointKeith

  172. When I initially left a comment I seem to have clicked on the -Notify me when new comments
    are added- checkbox and now every time a comment is added I recieve four emails
    with the same comment. Is there a way you can remove me from that
    service? Appreciate it!

Comments are closed.