Solved

Identify colors on a chart

Posted on 2013-06-14
12
355 Views
Last Modified: 2013-06-18
I selected specific colors from the color palate to build the chart shown below.

chart colors
I need to find out which colors these are so that I can build different chart (a bar chart) using the same colors. However, when I right-click on the series and go to Format Data Series -> Fill, I can't see the color that I chose. The dialog is set back to "Automatic" rather than toggled to "Solid Fill" showing the color that I have chosen.

This only occurs if I close the file and re-open it. The chart shows the correct color, but I cannot figure out how to determine which color it is.

Does anyone know how I can determine which colors I have on my chart? I would like to stay within Excel and not have to copy and paste to a graphics program and use the eyedropper.
0
Comment
Question by:dougf1r
  • 7
  • 5
12 Comments
 
LVL 40

Expert Comment

by:als315
ID: 39249592
Link color is in line's property Format.Line.ForeColor.RGB, but this property is filled only if color was set manually, so you can't get automatic colors. May be better to use this solution:
http://datapigtechnologies.com/blog/index.php/automatically-set-chart-series-colors-to-match-source-cell-colors/
and color your lines as you like?
0
 
LVL 1

Author Comment

by:dougf1r
ID: 39251456
I did set all of the colors manually (please see attached spreadsheet). I closed the spreadsheet and re-openend it and the toggle is now set to "Automatic" and does not show the "Solid Fill" color I chose for each area segment.

I am not aware of how to implement the VBA code Format.Line.ForColor.RGB you suggested.

The macro at the link provided may help. However, I would rather not re-build the color scheme I have already generated. I just need to identify the colors that I have already selected to display.
colors.xls
0
 
LVL 40

Expert Comment

by:als315
ID: 39252250
I can set colors for your chart and save it without problems. What Excel version is used? Do you have all updates installed?

Try this file. Allow macros, press button and first cell of ranges will be colored according to chart's colors.
colors.xls
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 1

Author Comment

by:dougf1r
ID: 39253711
I am using Excel 2010 (with updates current). I had saved it as version 2003 because that does copy/paste best into my version of CorelDraw for other graphics editing.

I may not be using the macro correctly...

I changed the cell colors to what I would like the chart to display. Then I click on the "Get Colors" button. However, rather than updating the chart colors with the cell colors, the cell colors revert back to what the chart colors are.
0
 
LVL 40

Accepted Solution

by:
als315 earned 500 total points
ID: 39254094
This sub in sample was modified to get colors from chart. Original sub from link above can set colors from cells.
In new sample with one button you can get colors from chart and with second - set colors from cells to chart
colors.xls
0
 
LVL 1

Author Comment

by:dougf1r
ID: 39254306
Set Colors works great. Thanks!

Get Colors gives an error "Sub or Function" not defined.
0
 
LVL 1

Author Closing Comment

by:dougf1r
ID: 39254319
I changed the Sub name to "GetColors" and it works just fine. Thanks for your help!
0
 
LVL 1

Author Comment

by:dougf1r
ID: 39254370
I have multiple charts on one tab. The macro seems to only apply to the first chart. Can the code for "SetColors" be adjusted to apply to all charts on the tab?
0
 
LVL 40

Expert Comment

by:als315
ID: 39256500
I didn't tested it with many charts, but code has loop across all charts. May be you can upload sample with many charts?
0
 
LVL 1

Author Comment

by:dougf1r
ID: 39256784
Here is a sample with many charts.
colors-manycharts.xls
0
 
LVL 40

Expert Comment

by:als315
ID: 39257480
Idea of sub was following: you should color first row of each data set and chart will be filled according to these colors.
If you like to color other charts according to first chart, you can test this sample, but you should have same quantity of columns in each data set and first chart is the first chart in order as they were inserted, not up to down (in your sample it is second chart - 0.5x Ambient N)
Select fill colors for this chart, press SetColorsFromFirstChart button and all other charts will be colored similar.
colors-manycharts.xls
0
 
LVL 1

Author Comment

by:dougf1r
ID: 39257559
Many thanks for this!
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question