Graph Colors to match cell colors

brazt1234 · Mar 19, 2014

Hi
I am new on this forum and hoping some of you can be of assistance.
I am trying to make my graph / column / pie chart the same colours as my Excel cell colours.
EG: I have 5 cells which are each a different colour I then make a chart with these 4 cells data but now want the cell colours to correspond with the graph colours.

I have researched allot online and people talk about a macro and a VB code. I have tried all of these but cannot come right. I am not a computer genius but do know my way around a computer and excel pretty well however can’t figure this out. Maybe if someone has a step by step guide I could follow because I’d really like to get this right.
Would appreciate the help.

Derek Brown · Mar 20, 2014

Hello and welcome to The Board.
Have you tried the following:
Color Pie Chart Slices to Match their Source Cells » Bacon Bits
I suggest that you first follow the example on that web page and then try applying the method to your worksheet.

brazt1234 · Mar 24, 2014

Yes I have tried to follow this word for word as this is exactly what I want to do.

I dont know how to run a macro so although the formula is there I don't know what to do with it.

Derek Brown · Mar 24, 2014

Perhaps the following will help:
Create a Macro in Excel - Excel VBA Easy
Getting Started with VBA in Excel 2010

brazt1234 · Mar 25, 2014

Thank you, you really helped me huge.

I got this going and it now looks great.

Anyone know how to do this with a Column graph ??

Derek Brown · Mar 25, 2014

In the code your will see references to xlPie, xlPieExploded etc., try changing to xlColumnClustered, xlColumnStacked and xlColumnStacked100.
That should work for column charts but be aware that there may be some types of charts where you will not be able to change the colours in this way.

brazt1234 · Mar 25, 2014

I must be putting the amended code in wrong as I cant get any Column charts to work.

Original code is:

Sub ColorPies()
Dim cht As ChartObject
Dim i As Integer
Dim vntValues As Variant
Dim s As String
Dim myseries As Series
For Each cht In ActiveSheet.ChartObjects
For Each myseries In cht.Chart.SeriesCollection
If myseries.ChartType <> xlPie Then GoTo SkipNotPie
s = Split(myseries.Formula, ",")(2)
vntValues = myseries.Values
For i = 1 To UBound(vntValues)
myseries.Points(i).Interior.Color = Range(s).Cells(i).Interior.Color
Next i
SkipNotPie:
Next myseries
Next cht
End Sub

I changed the code to:

Sub ColorColumn()
Dim cht As ChartObject
Dim i As Integer
Dim vntValues As Variant
Dim s As String
Dim myseries As Series

For Each cht In ActiveSheet.ChartObjects
For Each myseries In cht.Chart.SeriesCollection

If myseries.ChartType <> xlColumnStacked Then GoTo SkipNotColumn
s = Split(myseries.Formula, ",")(2)
vntValues = myseries.Values

For i = 1 To UBound(vntValues)
myseries.Points(i).Interior.Color = Range(s).Cells(i).Interior.Color
Next i
SkipNotColumn:
Next myseries
Next cht
End Sub

I tried a few versions and non worked.

Derek Brown · Mar 25, 2014

Apologies but I was looking at the version I had adapted for my use at work, so my instructions were not too clear.
What type of column chart are you using? I can get it to work on a clustered column chart by using xlColumnClustered.
Try removing the "If ..... SkipNotColumn" and see what you get.

Graph Colors to match cell colors

brazt1234

New Member

Excel Facts

Derek Brown

Well-known Member

brazt1234

New Member

Derek Brown

Well-known Member

brazt1234

New Member

Derek Brown

Well-known Member

brazt1234

New Member

Derek Brown

Well-known Member

Similar threads

Forum statistics

Share this page

Graph Colors to match cell colors

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock