Color points in a scatter plot based on a number

[hamang]

I have a simple scatter plot of X,Y points (data in columns A and B). I would like to color each point based on the value in column C.

I would like the colors to change smoothly and have a good range.

If anyone has an example workbook of something similar to this I would be grateful.

[MrShorty]

The standard approach to conditional formatting in charts is to add each color/condition as a separate data series, where your conditions form the basis of formulas that decide which data series each point belongs in. http://peltiertech.com/conditional-f...-excel-charts/

[hamang]

The standard approach to conditional formatting in charts is to add each color/condition as a separate data series, where your conditions form the basis of formulas that decide which data series each point belongs in. http://peltiertech.com/conditional-f...-excel-charts/

Thanks for the link. It's not a bad approach and would work ok with my data. I would prefer though a VBA solution. I found some code (http://stackoverflow.com/questions/3...16834#41716834) that should do what I want (it uses a finely varying color map pasted in to a worksheet) but I can't get it to run without error. If anyone could get it working on a simple example workbook I would be very grateful.

[Kaper]

Have you tried to do it step by step as explained in linked page (including color lookup table preparation)?
You also not refer what particular error you received.

Anyway, see how it works in attached example

[hamang]

Have you tried to do it step by step as explained in linked page (including color lookup table preparation)?
You also not refer what particular error you received.

Anyway, see how it works in attached example

Hi Kaper,

Thanks very much for getting this working. My attempt was somewhat similar to yours (I had copied the colour map over and that was working) but the code that I pasted in wasn't compiling. I will compare later with yours to work out where I was going wrong.

Just one quick question if I may. I added some random numbers in column K (values in range 70 to 80) and then changed the 'h' in the code to 'k' and the points all plot out as blue. I was hoping that the code would find the maximum and minimum and then distribute the available colours (255 colours) over the new values (i.e. 255 colours over a range of 10). Is there another line that needs changing?

Thanks,

[hamang]

Ok, I managed to figure out how to change the limits and it now spreads the colours over the range of values.
Thanks,

[Kaper]

Glad to hear that, and thanks for marking thread as Solved and for reputation added.