Formatting cells with gradient colours

[bobhc]

Good day

I am using excel 2010 and I am trying to fill a small range of cells with gradient colours. The 4.5 to 7.7 would be green and then the gradient would go through amber to red at the highest and lowest numbers. I have merged two cells in to one on each row to enable to cells to take the data. The range is blood sugar readings from comma to hyper. I have tried to use the conditional formating-colour scales on the ribbon but nothing happens, the cells are selected but as soon as I mouse over to the colour schemes the selection disappears?

16.7mmol/L

14.5-16.7 mmol/L

12.3-14.4 mmol/L

10.1-12.2 mmol/L

708-10.0 mmol/L

6.2-7.7 mmol/L

4.5-6.1 mmol/L

2.8-4.4 mmol/L

<2.8 mmol/L

 

[NARAYANK991]

Hi Bob ,


I have not understood your requirement correctly ; can you confirm ?


1. There are the following values :

[pre]

< 2.8
2.8   to   4.4
4.5   to   6.1
6.2   to   7.7
7.8   to  10.0

[/pre]
There are more values , but as far as the formatting is concerned , I don't think they are relevant.


You say that values in the range 4.5 to 7.7 should be coloured GREEN ; can you specify the values range for AMBER and RED ?


Narayan

 

[SirJB7]

Hi, b(ut)ob(ut)hc!


Give a look at this file:

http://dl.dropbox.com/u/60558749/Formatting%20cells%20with%20gradient%20colours%20%28for%20bobhc%20at%20chandoo.org%29.xlsx


Hope it helps.


If you have chosen a standard color as provided by Excel (I agree that they're useless for this case) it'd have been much simpler than CF each range.


Regards!

 

[bobhc]

Good day NARAYANK991 and SirJB7

NARAYANK991 you have understood perfectly,and SirJB7 your colours would work but I see them as for individual cells, what I am trying to do is start of at the two cells containing the range from 4.5 to 7.7 with green and the go through amber to red in a gradient fill over the rest of the two ranges, upper and lower, I realise it would not be much of a gradient on the two lower cells but I am hoping it would show a better fill on the upper ranges. I am trying to do this so that it is a visual aid to the diabetic so that the colour gradient would show their 'safe' levels more clearly.

 

[SirJB7]

Hi, b(ut)ob(ut)hc!


Good day for you too, old dog. From a little puppy, of course.

And yes, I've defined the colors accordingly with the range values you provided.

And no, they don't work for individual values, even the change at those values

If you provide a full scale from 0 to 20(?) maybe something different can't be arranged.


BTW, Excel has apparently an issue regarding gradients: even it let you define two gradients as you asked (download again the file, alternatives 1-12 are the default and look at the CF of 13th), it has problems to display them. Seems to be a but, either for wrong displaying or for letting two gradients for same range.


Select N2:N22, CF, edit, and look inside.


Regards!


EDIT: bug, not but... are you confusing me?

 

[SirJB7]

Hi, b(ut)ob(ut)hc!


Setting green as first rule, gradient from amber to red as second and gradient from red to amber as third, is the nearest that I can arrive.


Regards!

 

[bobhc]

Good day Gentlemen I have uploaded the chart, i hope you will make sense of what I am trying to do the link is https://docs.google.com/spreadsheet/ccc?key=0AjSJrMR-QH5bdHpuckU3VEE4cFY2bm1GVWxxS19FTlE.....but SirJB7 your colour for the 99.9 may be right but I would have put a picture of a wreath, hearse and funeral procession

 

[SirJB7]

Hi, b(ut)ob(ut)hc!


Found a trick at:

http://blogs.office.com/b/microsoft-excel/archive/2006/02/24/conditional-formatting-trick-1-multi-coloured-data-bars.aspx

that let's you achieve your goal.


Download now this file (same as previous but .xlsm):

http://dl.dropbox.com/u/60558749/Formatting%20cells%20with%20gradient%20colours%20%28for%20bobhc%20at%20chandoo.org%29.xlsm


If you want to read the first link and then press Alt-F11 and check the VBA code for Hoja1, you'll find the trick... indeed quite tricky!


Regards!


PS: now I'm going to read your last post


PS2: too much work adding an image with crosses and coffins... maybe the intro video of 2008 film Dead At The Funeral, have you seen it?

 

[SirJB7]

Hi, b(ut)ob(ut)hc!

Can you please repost the link, please? I'm getting an error when trying to download.

Regards!


EDIT: previous link updated, sorry

 

[bobhc]

Hope this link works https://docs.google.com/spreadsheet/ccc?key=0AjSJrMR-QH5bdHpuckU3VEE4cFY2bm1GVWxxS19FTlE

 

[SirJB7]

Hi, b(ut)ob(ut)c!


'til now, my best shot:

http://dl.dropbox.com/u/60558749/Formatting%20cells%20with%20gradient%20colours%20-%20Insulin%20Readings%20%28for%20bobhc%20at%20chandoo.org%29.xls


Regards!

 

[bobhc]

SirJB7, re your best shot, that works. and If I have no joy in what I am trying to achieve I will go that way......but, what I am trying to do is get the gradient colours in the the cells that hold the actual data, to the left of the cells holding your colours, I think the problem I am having and the reason the colours do not show is the fact that the cells holding the mmol/L numbers are merged cells

 

[SirJB7]

Hi, b(ut)ob(ut)c!

Download file again. Slightly modified.

Only left replacing multi cells "To high" and "To low" for single cells to have the exact same color that previous cols. But not gradient.

Regards!