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”.
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.
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.
Add the following JavaScript to the HTML Source dialog.
<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.
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.
Hi Arild,
this post is great, you have found a wonderful solution. I still wonder why MS has removed the conditional formatting with SharePoint!!!
I could manage to add your code to my lists and works very well.
The only limit is the filtering, grouping, and searching that is not re-coloring. I tried also Dan’s suggestion “window.onhashchange = formatList;” but I am not a programmer and I find it difficult to colocate in the script.
Can you tell us how to do it? I would be interested also in the dates and values code too.
If anyone else would contribute with the code is more than welcome and appreciated.
Thank you and keep up the good job.
Have a nice day, Giorgio
Hi Dan,
good tips but it does not work for me.
I am not an expert, I could make all the rest working but filtering, grouping, and searching are always returning no conditional formatting.
Could you post the code?
Thank you. Have a nice day, Giorgio.
This has been an awesome help. Thanks for the great (yet straight forward) instructions. With the help from the comments, I now have the individual cells working. Thanks
Perfect solution…
Is there any way to make this works with BCS as external content type ?
Hi,
If you put the formating in a function then call it as below, it will work after filtering:
window.onhashchange = formatList;
This still doesn’t work on document libraries if the user drags and drops the document.
This works brilliantly and so simple, so glad I found your post, thanks!
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.
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.
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!
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?
Is there a way to highlight just the Active row of a list?
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…
How did you manage to change it so that the colour just appears on the cell not the whole row?
Kathy,
can you please give me the hint how to chance the example code such that it colors only a spefific cell and not the entire line ?
Thank you!
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
This works Perfect 🙂
Could you please provide me a way to have this implemented in the Datasheet view also?
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?
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.
Thanks! This works great on a simple list – have you been able to get it working on a Grouped list view?
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.
I will try and answer your questions later. My brain is in vacation mode for one more week at least
Thank you so much for this simple solution!!! Your screen shots and instructions were very helpful and very accurate!
Very simple and effective. Thank you.
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”);
});
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!
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”);
});
Thanks a lot, very simple, very clear.
Hello . I’m from Brazil. Nice post . Incredible its simplicity. Thank you !
Incredible. Worked like a charm. Thank you very much. Been searching for this solution for a long time.
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
I managed to figure it out. Thanks!
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.
Could you post your solution to the number column only? Struggling to figure it out!
Thanks!
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!
Glad you fixed the problem. Thanks for commenting.
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)!
Have you found a solution for this problem?
Thanks for this; genius in its simplicity!
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.
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 🙂
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!
I have since figured it out. I viewed the source code and found the exact column name and that worked great! Thank you!
Glad you found a solution.
Hi, thanks for the response. This works great on-screen, but they wanted to be print the list and still have the colors print. When I do a print preview, there are no BG colors. If it’s not thing it’s another. Thanks for your help.
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
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.
This column only version also works perfectly. Thank you for a great simple and effective solution !