Solved

Identify colors on a chart

Posted on 2013-06-14
12
320 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

758 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

16 Experts available now in Live!

Get 1:1 Help Now