Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Graph Colors to match cell colors

  1. #1
    New Member
    Join Date
    Mar 2014
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Graph Colors to match cell colors

    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 cant figure this out. Maybe if someone has a step by step guide I could follow because Id really like to get this right.
    Would appreciate the help.

  2. #2
    Board Regular
    Join Date
    Dec 2005
    Location
    Basingstoke (UK)
    Posts
    2,390
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Graph Colors to match cell colors

    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.
    Never give way to anger - otherwise in one day you could burn up the wood that you collected in many bitter weeks.

  3. #3
    New Member
    Join Date
    Mar 2014
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Graph Colors to match cell colors

    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.

  4. #4
    Board Regular
    Join Date
    Dec 2005
    Location
    Basingstoke (UK)
    Posts
    2,390
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Graph Colors to match cell colors

    Never give way to anger - otherwise in one day you could burn up the wood that you collected in many bitter weeks.

  5. #5
    New Member
    Join Date
    Mar 2014
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Graph Colors to match cell colors

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

  6. #6
    Board Regular
    Join Date
    Dec 2005
    Location
    Basingstoke (UK)
    Posts
    2,390
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Graph Colors to match cell colors

    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.
    Never give way to anger - otherwise in one day you could burn up the wood that you collected in many bitter weeks.

  7. #7
    New Member
    Join Date
    Mar 2014
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Graph Colors to match cell colors

    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.

  8. #8
    Board Regular
    Join Date
    Dec 2005
    Location
    Basingstoke (UK)
    Posts
    2,390
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Graph Colors to match cell colors

    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.
    Never give way to anger - otherwise in one day you could burn up the wood that you collected in many bitter weeks.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •