Solved

Identify colors on a chart

Posted on 2013-06-14
12
342 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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

ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

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

Suggested Solutions

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
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.

770 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