Conditional formatting in SharePoint 2013 and Office 365

Print Friendly, PDF & Email

Sometimes the requirement comes up to make conditional formatting in a SharePoint list or document library. There are several ways of achieving this, you can buy a solution, the are several solutions out there providing conditional formatting. Or you can use SharePoint designer etc.

I will describe a simple method using jQuery that will work both on SharePoint 2013 on-premises and on SharePoint online in Office 365.

 

In this example I created a custom list with a set of columns. One of the columns, “Approve” is a Choice column with a couple of choices , “None”, “Approved” and “Approved under revision”.

2014-09-18_09h49_33

These are the choices we will use for our conditional formatting, We will make items tagged with “Approved under revision” a yellow background and items tagged with “Approved” a green background.

Navigate to your list or document library and select Edit Page from the settings menu.

2014-09-18_12h07_34 Then click Add a Web Part and add a Content editor webpart to this page. We will use this Content editor webpart to add our jQuery script.

Click anywhere in the Content editor webpart and then click Edit Source on the ribbon.

2014-09-18_12h09_58

Add the following JavaScript to the HTML Source dialog.

2014-09-18_12h12_09

 

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js" type="text/javascript"></script><script>

 

    $(document).ready(function(){

        $Text = $("td .ms-vb2:contains('Approved')").filter(function() {
  return $(this).text() == "Approved";})
        $Text.parent().css("background-color", "#00FF66");
        
        $Text = $("td .ms-vb2:contains('Approved under revision')");
        $Text.parent().css("background-color", "#FFFF66");

        
    }); </script>

Line 1 of the script points in this example to the jQuery library online. In your environment you could just as easily download the jQuery  library form here and put in in a library on your SharePoint or Office 365 site. I usually put it in the Site Assets library and update the URL in line 1 accordingly.

Line 7 searches for a table cell with the word “Approved” in it and uses the filter function to make sure it matches the word exactly since the next item we are looking for “Approved under revision” alos includes the word approved. Once it find a table cell with the word “Approved” it sets the background color for the parent to green. The parent in this case will be the table row so the hole row gets a nice green background color.

Line 11 and 12 does the same thing, without the filter function since we are searching for a match to “Approved under revision”, and sets the background color of the parent to yellow.

 

Click Ok and the Stop Editing in the ribbon when you have pasted in the script.

The result will look like the screenshot below.

 

2014-09-16_10h10_17

So that was my description of an easy way to get conditional formatting for a list or document library. Works great both in SharePoint 2013 on-premises and in Office 365.

You may also like...

42 Responses

  1. Debbie says:

    This works brilliantly and so simple, so glad I found your post, thanks!

  2. Shawn Harris says:

    How does one apply this to a datasheet view – I see someone has already posed that question, but I can’t seem to find a remedy.

  3. Martin Lofgren says:

    How do you make this work after sorting on any column? The conditional formatting using colours in my case is only there when opening the list using it’s predefined settings. When sorting or searching the records the colouring is lost until I click the list from scratch agian.

  4. Adam says:

    Best and most simple instructions I’ve seen on the internet and I’ve been researching for days. Most people suggest complicated edits in Designer. Little did I know Designer wasn’t even necessary. I’ll primarily use JQuery now.

    Thank you!

  5. Adam says:

    I’m trying to use this to highlight items marked “Closed” Yellow. I put the script in and I can get it to turn items yellow when I’m in datasheet view, but when I switch back to just showing the list it loses all the formatting. Why would it show up for datasheet view and not normal view?

  6. RedHeadedMonster says:

    Is there a way to highlight just the Active row of a list?

  7. Kathy says:

    This worked perfectly and I was able to modify it to the actual cell, thereby having a dashboard effect. However I noticed if I tried to using grouping or different view style it doesn’t work. Any way to work around this? Thank you again…

  8. Ben says:

    I am utilising this on a document library and it works perfectly. However I also have a calendar view of the document library and wanted to see the colours there as well. Any ideas.

    Kind regards
    Ben

  9. SUjith says:

    This works Perfect 🙂
    Could you please provide me a way to have this implemented in the Datasheet view also?

  10. Nort Green says:

    Hi, excellent job, i have used this to highlight the status, but would it be possible somehow to check if a date is overdue?

    If i have a status that is not Done or Cancelled, and a date that is overdue, can i somehow highlight the date then?

    • Arild Aarnes says:

      Yes it is definitely possible to change color based on date. I have not done this so I do not have the syntax I’m afraid.
      Do a Google search for javascript or Jquery date validation and you should be able to find something.

  11. Eric J. says:

    Thanks! This works great on a simple list – have you been able to get it working on a Grouped list view?

    • Arild Aarnes says:

      I have not tried with a group view yet. But it should work there as well. It’s just a question of finding the correct item for the jquery to change the color on.

  12. Arild Aarnes says:

    I will try and answer your questions later. My brain is in vacation mode for one more week at least

  13. Becky says:

    Thank you so much for this simple solution!!! Your screen shots and instructions were very helpful and very accurate!

  14. Javed says:

    Very simple and effective. Thank you.

  15. JerryFromFL says:

    I am so happy that I found your blog post on conditional formatting! I have tried to implement this on my list in SharePoint 2013 and unfortunately, I cannot make it work. Is there something that I am doing wrong or did Microsoft change something that doesn’t allow this to work anymore?

    My edits below:

    $(document).ready(function(){
    $Text = $(“td .ms-vb2:contains(‘Urgent’)”);
    $Text.parent().css(“background-color”, “#B00000”);
    });

  16. Lawrence says:

    This is some amazingly simple code. I have a question that I really would appreciate help on though. I am highlighting a row when a specific column reads ‘yes’. The issue I have is the row will highlight when other columns read ‘yes’ too, even if the column I am interested in reads no. Is there a way to isolate it to only look at the info in a specific column? Thanks!

    • Lawrence says:

      This is the code that I have tried to put together, but it is not yet working for me 🙁 The column I am looking to be the controlling column for yes/no is labelled ‘Technology’. Thanks in advance!

      $(document).ready(function(){
      $(“th .ms-vh2:contains(‘Technology’)”)
      $Text = $(“td .ms-vb2:contains(‘Yes’)”);
      $Text.parent().css(“background-color”, “#b1d4a2”);
      });

  17. Marco says:

    Thanks a lot, very simple, very clear.

  18. Rafael says:

    Hello . I’m from Brazil. Nice post . Incredible its simplicity. Thank you !

  19. Babur says:

    Incredible. Worked like a charm. Thank you very much. Been searching for this solution for a long time.

  20. JCR says:

    Hello Arild,

    I am not so experienced with Jquery. Your solution is one of the best I have found online to solve this problem. I have a list where I am using the code to set conditional formatting. I have restricted it so that it colors only the column that has the key words (“approved” “not approved”). I was wondering – what must I change in this code in order to have it reference a column with numbers.

    I have a calculated column of “total hours” column in my list, which contains aggregate totals of hours. So it has numbers anywhere from 0 to 100. I would like to use conditional formatting to color the numbers that fall in certain ranges. For example – <=40 would be red. 41 – 50 would be yellow. 50<= would be green.

    Is it easy enough for me to possibly just use a reference instead of $text (maybe $number) so that the conditional formatting would apply to that number column only? Thanks!

    – J

    • JCR says:

      I managed to figure it out. Thanks!

      • Arild Aarnes says:

        Hi, glad you figured it out. I have not found the time to look at this yet 🙂

        You are more that welcome to share the solution in a comment here if you like.

  21. Ron says:

    Fixed my own issue. All I needed to do was to turn server rending on (checkbox in miscellanous category of web part properties). This enabled me to use a URL without the #inplview that works with the conditional formatting. Thanks again – simple fix!

  22. Ron says:

    Thank you – it is working for me if I use the aspx url for the page. However, I am providing the users a flitered column version of the webpage url (with inplview). Since inplview is part of my filtered url, it takes away the conditional formatting. Please help in what the filtered URL needs to be in order for the conditiona lformatting to remain (or other solution, like changes to the script you provided)!

  23. Dave Starkey says:

    Thanks for this; genius in its simplicity!

  24. Kevin says:

    How could I do formatting to check if a date was greater than something and then highlight the row or date another color?

    BTW, this page here helped me even get any conditional formatting working on my Office 365 Sharepoint! The new Sharepoint Designer 2013 is missing a lot of features and your solution was great! Thanks.

    • Arild Aarnes says:

      Yes it is definitely possible to change color based on date. I have not done this so I do not have the syntax I’m afraid.
      Do a Google search for javascript or Jquery date validation and you should be able to find something.
      If I have time one day I will give it a try and post it here but that might take a while 🙂

  25. Michelle says:

    I wanted to do the conditional formatting on a column that has people’s names. I tried entering the display name and their AD alias, but it’s not doing anything. Thank you!

  26. shashi says:

    Thanks a lot.

    In case i want to change the background color for the column only and not the entire line, what will be the code

    • Arild Aarnes says:

      Hi,

      I’ve not tested this now but I think if you remove .parent for the line $Text.parent().css(“background-color”, “#00FF66”); so it reads $Text.css(“background-color”, “#00FF66”); it will change only the table cell and not the entire row.

      • Jimmy says:

        This column only version also works perfectly. Thank you for a great simple and effective solution !

  1. April 5, 2016

    […] How to… […]

  2. September 16, 2016

    […] The method was provided by Arild Aarnes on SharePoint247 blog […]

  3. February 8, 2017

    […] The method was provided by Arild Aarnes on SharePoint247 blog […]

Leave a Reply

Your email address will not be published. Required fields are marked *