Color Chart Data Points By Cell Color

[smile0]

Hello, I'm looking for a way to color the chart data by cell color or RGB values in a cell.

I don't know VBA, can this be done?

[smile0]

I found a thread at another forum where a guy posted macro to do this, but I can't seem to find it, just a bunch of vba to put labels on chart etc.
Thre thread is very old and I don't think I get reply from him, any ideas. Am I blind and don't see the macro?

http://www.ozgrid.com/forum/showthre...566#post370566

[Andy Pope]

The code is in the worksheet object, rather than a standard code module, because it is run when the selection_change event fires.

[smile0]

The code is in the worksheet object, rather than a standard code module, because it is run when the selection_change event fires.

OK, I found the code placed it into a button, but the code will not run. I have attached my file.

Please Login or Register  to view this content.

[Andy Pope]

You can not just paste an event procedure into the middle of a routine.

Try this simplified code instead.

Please Login or Register  to view this content.

[smile0]

Thank you, it works perfect.
The range in the code can be changed if I need to color from different cells right.

[Andy Pope]

Yes. Either directly in the code or you could set up a named range and then reference that in the code.

[smile0]

If a graph would contain multiple series, say 2 for example. Is it possible to choose what colors are used for what series, to be able to paint them separately?

[Andy Pope]

Yes.

You would need to loop through series collection and use alternative ranges for colours depending on series.

[smile0]

What changes I need to make to the code to paint series2 with alternate cell range ActiveSheet.Range("G8:G27")?

I tried copying ang changing the code but error is all I get. How to refer to series2.

[Andy Pope]

Please Login or Register  to view this content.

[smile0]

Your code works fine, but I wanted to have 2 different color ranges, separate for each series in the chart.
Please see the attached xls file, I wanted to have an easy way to change, add series that is why i call next piece of code like this.
Can you make this work?

[Andy Pope]

There are two separate ranges being used for each series.
First series uses D8:D27 then the second series uses the OFFSET function to redefine the colors to come from range G8:G27.

I simply copied the 3 columns of data and altered the xy values. I did not bother to change the colours being used in G8:G27. If you chaneg those and re run the code you will see the difference.

[smile0]

There are two separate ranges being used for each series.
First series uses D8:D27 then the second series uses the OFFSET function to redefine the colors to come from range G8:G27.

I simply copied the 3 columns of data and altered the xy values. I did not bother to change the colours being used in G8:G27. If you chaneg those and re run the code you will see the difference.

Then your code does not work, to make sure I changed range G8:G27 colors to single color and yet they were colored with various colors.

Here is my code, could you make it work? I don't like offsets it's hard to use when sheet size is large.

Please Login or Register  to view this content.

[Andy Pope]

What's the sheet size got to do with using offset in this case?

I should have placed the range re-assignment within the series loop.

Please Login or Register  to view this content.

In the second routine you need to reference the 2nd item in the series collection. Otherwise you will format the 1st series again.

Please Login or Register  to view this content.

[smile0]

The both codes works OK now. Thank you.

What's the sheet size got to do with using offset in this case?

The offset is static and once the sheet size is large it's very hard to recalculate (count cells to the righ, left etc.) it again if a rows or columns are inseted to the worksheet.

I have used some offset pieces of code to convert the cells to RGB because I was told that the fuctions does not handle ranges from separate sheets. It was nightmare to keep the code working after I had to add some rows and colums to acomodate aditional data.

Can't the code be changed to take data from range, and paint a range too?
Code takes RGB values from sheet LAB2RGB!F8:F27
Paints a range in antoher sheet Color_No with offset is it possible to make it a range instead?

Please Login or Register  to view this content.

[Andy Pope]

You can write yourself a routine to colour cells given input ranges. And the information can come from a different sheet to the output.

Puts some rgb values in A30:C32 of Sheet1 and Sheet2!A1:A3 will be coloured.

Please Login or Register  to view this content.

[smile0]

Thank you again, your code works perfect. I will adapt your code to my document.

[smile0]

If I need to refer to a chart not on activesheet, like I want the code to be executed on opening the file or to run from another piece of code.

I tried to change the:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

It works fine but how to refer to a second, third etc. chart on the sheet?

It seems I needed to change the ChartObjects(1) to ChartObjects(2) etc. can't I select chart based on chart name ?

[smile0]

I also found a bug that grayscale colors are not grayscale on the graph, why is this limitation of excel?

[Andy Pope]

Please Login or Register  to view this content.

Move the dots to a white area of the chart, still think there purple?

[smile0]

Yes, WHen color is near grayscale the colors are very wrong, and the don't match the RGB values.

I have included a screenshot of the graph on white background - the RGB numbers speak for themselves.
I have updatated the colorpoints-purpleproblem2.xlsm with RGB data on "LAB2RGB" sheet, can you try to read that RGB data for coloring the chart data points? Maybe then it will read correct values, beacause cells themselves are colored correctly.

AFAIK, you just need to change this piece of code you wrote earlier to paint the cells of a sheet.

Please Login or Register  to view this content.

[Andy Pope]

The problem is you used ColorIndex instead of Color. ColorIndex is a value 1 to 56. Color is RGB value.

See attached which has the grey cells with their rgb and colorindex values.
The chart has 2 series with colorindex and color values applied and displayed via their data labels.

[smile0]

Yes I thought too that colorindex was a problem, because it colored some point correctly. Thanks.

It would still be nice if I could paint the chart with RGB values,
I tried to change your code but it seems I don't know what i'm doing

Please Login or Register  to view this content.

[smile0]

Here is my file with the above code, when you have time please take a look at it.
I just visited your website is great, many examples. Seems like you are Excel VBA chart expert

[Andy Pope]

The code was for applying rgb color to cell. You now appear to be trying to alter the chart points directly but you have not change the variable types.
Also you have RGB values that are floating point instead of integer values.

Why not explain what you are trying to do?

[smile0]

The code was for applying rgb color to cell. You now appear to be trying to alter the chart points directly but you have not change the variable types.
Also you have RGB values that are floating point instead of integer values.

Why not explain what you are trying to do?

In my case I initialy wanted to paint chart series by using same colors and in worksheet cells, but later I remembered that on some sheets the cells have a gap between them in various places for presentation reasons (I had to enter captions, names etc.).

The RGB data is stored in "LAB2RGB" sheet that does not have any gaps, so it would be better to use that centrally stored RGB values rather than cell colors. I was trying to make the code you wrote work, but I learn VBA by example (I'm not a programmer) so when it goes to changing stuff most of the time it does not work

I wanted to read RGB data in sheet LAB2RGB cells range A8:A27 for Red, range A8:A27 for Green, range C8:C27 for Blue. Then to color the graph on sheet1 the series points like on previous xls files where the color data was read from a color in a cell.

[Andy Pope]

Please Login or Register  to view this content.

[smile0]

thank you, the code works fine now.

If painting second series what is better?

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[Andy Pope]

The 2nd set where you make use of the same single function.

[smile0]

The 2nd set where you make use of the same single function.

Thank you again for your help.