Solved

Identify colors on a chart

Posted on 2013-06-14
12
334 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 39

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 39

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
 
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 39

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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 39

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 39

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
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 demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

864 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now