XY scatter plot colored by Z

[geophysicist]

Hi,

I'm looking for VBA code to color an XY Scatter chart by Z (i.e. another property value in an adjacent column).
Ideally this would also display a colorbar.

Thanks!
geophysicist

[Andy Pope]

something like this should get you started.

Please Login or Register  to view this content.

The legend in M2:M11 is a simple 1-10 scale using conditional formatting.

[geophysicist]

Andy,

Thanks for tackling my problem. The code you provided was indeed a very good starting point, as I had absolutely no idea where to begin. The guts of it gave me enough to go play with, though. Also, I like the clever use of conditional formatting to make the colorbar. Great, simple idea.

In the course of using that first-pass code, I discovered a few functionality pitfalls. The most critical one was that the colorbar values were not referenced, only the element number of the colorbar cells. The other issue was that Z-values < 1 and Z-values > the number of cells in the colorbar were all painted white.

I was able to resolve these issues on my own and present my final code here, along with a new copy of the spreadsheet showing its development.

Please Login or Register  to view this content.

This is such a basic functionality gap (coloring XY data by Z data) that I can't believe Excel hasn't done this themselves.
I was also surprised not to be able to find this kind of VBA code very easily on my online search.
So, I hope someone actually refers to this post and gets some value out of it.
If so, please * me.


Cheers,
g

[Andy Pope]

Although the colouration is not built-in you can do it without code by using multiple series and formula.

Also the 56 colour limit was for xl2003 and older.

[wineandyoga]

This is brilliant! Thanks so much for posting the solution.