When I switched from Office 2003 to 2010, one of the most liberating updates was the wildly expanded conditional formatting, previously limited to just 3 conditions and some pretty basic cell formatting.
Data bars caught my attention immediately as a great way to visualise a few rows of data quickly without having to make a graph.

One complaint – you’re limited to having all the bars the same colour . . . apparently.

One specific use of these data bars in a cell – as progress bars for tasks/activities in a project – looks pretty lame in monochrome. Wouldn’t it be great to have different colours for each activity based on the percentage of completion? Red for less than 20% (needs attention), orange up to 60%, dark green up to 80% and a really bright green for the final 20%! Oh and must have a completely different colour for 100% – meaning finished and closed.

So I set about seeing how to get from monochrome default to psychedelia.

I make no apologies for writing “colour” – I’m British. 🙂

It MUST be possible to easily work around something so basic, I thought. But looking through forums and generally googling, all I could really find was a post from 2006 by Diego Oppenheimer, who was in the Excel team at the time and now runs Algorithmia. this was before Office 2007 had even been released so we hadn’t even started finding out the good things about data bars and here was one of the developers saying there’s a minus point (but at the same time revealing the workaround):

The Excel 2007 UI allows you to choose whatever colour you want for your data bars, but, by default, all the data bars you apply to a range have to be the same colour. Someone on our team recently showed me how to use a tiny bit of VBA to simulate having multiple colours of data bars on a range conditionally applied, so I thought I would pass along the trick.
Say, for example, you are looking at student grades, and you want all the data bars for students with a passing mark (60%+ and above) to be green, and those with a failing grade (59% or less) to be red. The first thing you would do is to add some red data bars to your data, and then some green data bars. By default, Excel shows you the last set applied, so the data bars would be green. If you then launch the VB Editor (Alt + F11) and in the immediate window (Ctrl+G), type:selection.FormatConditions(1).formula = “=if(c3>59, true, false)”

So there was the secret. And apparently it was still a secret because nobody seemed to have posted much about it to the forums – no working examples, no clear explanations for the inexperienced – things the Excel communities are generally very good at.

What I got to in the end after a few days of playing around, is a self-contained userform that allows the user to set colours and limits for up to 11 value ranges and then apply all this to the current selection. Why 11? Well as in the example I gave, it all started from the idea of percentages – and ten sub-divisions come naturally – plus that final colour for “completed”. You don’t have to use all 11 and the value range can be anything you want, not just 0 to 100 which was my original need.

Basically it creates 11 formatting conditions for the selection, all of them displaying simultaneously.

The order that the conditions appear in the list is the priority Excel gives them. The trick of Mr. Oppenheimer’s formula is to hide all the colours higher in the list that don’t meet the “>” requirement.

The form works on the current selection so the first step is to select all the cells where you want the same set of conditions to apply. Then show the form (custom button on the ribbon or some object on the worksheet with an assigned macro, for example).

N.B. If you’ve already got some conditional formatting set up for these cells, the form will try to read as much as it can. More importantly, when you click “apply” you will lose all the previous formatting.

Choosing the colours

I incorporated a technique to summon the default colour picker to colour the buttons instead of spending hours coding something. It colours a cell, memorises the value then sets the cell colour back to the original.

So simply click on a button and pick a colour. This is fine for changing a single colour but it’s not the best way to organise a complete matching set. So…

Preset colour themes

During testing I found myself having to set 11 colours and 11 limits every 3 or 4 minutes. An hour of that got me pretty pissed off so I built in some preset colour themes. You can easily add your own too.

Colours in Excel can be represented as a number so the preset colour themes are just arrays of 11 numbers built into the UserForm.

When you select “Light blues” form the preset list, the VBA that gets used is this:

The “fade” buttons

These let you choose just one colour for button/range 1 and the VBA will calculate progressively lighter or darker shades.

Get the cell fill colours from a worksheet

I also found it much quicker to compose sets of colours I liked by colouring cells directly on a worksheet rather than clicking buttons on the form (changing a button colour is three clicks every time). So prepare your range of coloured cells on the same sheet as the range of data where you want to put the data bars. Click “select coloured range”, select the cells, then click “set”.

Set the ranges

You must set the global minimum and maximum limits for the values you want to display as data bars.

At the moment you don’t have all the options of the standard Excel setup – no percentages, percentiles or formulas, no automatic calculation of the highest value in the range, only numbers.

You must also set the maximum value for each range. If you’re using 10 or 11 ranges, you can set the global limits the use the “spread” button to do the work for you.

I would appreciate comments and criticisms to improve my coding. I plan to incorporate as many of the standard data bar options as possible and any suggestions or requests for additional functions would be welcome.

Mi piace:

Mi piaceCaricamento...

Informazioni su DiGiMac

I'm an ex-pat Londoner. I've been living near Lake Garda in North Italy for more than 30 years.

12 risposte a Multicoloured Data Bars in Excel

Hey DigiMac,
I’ve been looking for a code like this for a while now so i really appreciate you sharing. I was wondering if you could explain how to use the program once we have downloaded the dropbox link. I am having trouble opening the two files. I would assume I need to open it in VBA- however, it isn’t working as well as I was hoping. Could you explain this?
Thanks,
Metfan

Hi Metfan,
I MUST put up a proper page for the files with more explanations and samples. I will also put up the latest version. Probably this evening…
But quick n dirty, you have to open a VBA project and import the form.

Maybe I’m missing something, but I can’t get it to work when the min-max range is [0,1] and the cell values are like 0.5, but seems to work fine when the range is like [0,25] and the cell values are integer.

I was using your work and it seems like such a useful task but I can’t seem to get it to work? I tried doing your last example following exactly the options you have chosen however the data bars are only coloured in the first colour (red). Green and amber do not appear at all and I don’t know what I’m doing wrong. Would appreciate the help!

Hi, I’m sorry Vineet and everyone else who has visited and tried to download the zip.
I’ve substituted the file on Dropbox and updated the link on both pages. Hopefully now you’ll be able to try it out.
I’m ashamed of how I’ve neglected the site as I know how annoying it is to read about something and think it sounds great then you discover the link is broken or corrupt or just doesn’t work.
I have only tested it in Excel 2010 under Windows 7 but I’ll do everything I can to solve any problems that arise

can you tell me where the macro or vbscript is that sets the upper bound and lower bound values for the conditional formatting? I’d like to modify the script so the upper bound is a variable set by a cell in the workbook.