Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 381
  • Last Modified:

Identify colors on a chart

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
dougf1r
Asked:
dougf1r
  • 7
  • 5
1 Solution
 
als315Commented:
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
 
dougf1rAuthor Commented:
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
 
als315Commented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
dougf1rAuthor Commented:
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
 
als315Commented:
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
 
dougf1rAuthor Commented:
Set Colors works great. Thanks!

Get Colors gives an error "Sub or Function" not defined.
0
 
dougf1rAuthor Commented:
I changed the Sub name to "GetColors" and it works just fine. Thanks for your help!
0
 
dougf1rAuthor Commented:
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
 
als315Commented:
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
 
dougf1rAuthor Commented:
Here is a sample with many charts.
colors-manycharts.xls
0
 
als315Commented:
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
 
dougf1rAuthor Commented:
Many thanks for this!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now