Excel Custom Coloring Charts by RGB number in cell

[waller99]

I have some excel sheets that are formatted like the following:


COMPANY | TOTAL | RGB
company1 | 10 | 255,000,000
company2 | 20 | 000,255,000
company3 | 30 | 000,000,255
...


My question is that I would like to have a macro that runs on this basic file and creates a bar graph with the data. Then it utilizes the RGB values in the column to change the specific bar for that row. Is this possible, and if so, can someone point me in the right direction? I know a little VBA but I can't figure out how to do this.

Thanks in advance..

Dustin

 

[waller99]

Can anyone help me with this? Thanks.

 

[ji32m]

    ActiveSheet.Shapes.AddChart.Select
    With ActiveChart
     .ChartType = xlPie
     .SetSourceData Source:=Range("Sheet1!$A$3:$B$5")
     .SeriesCollection(1).Select
    End With
    
    ActiveChart.SeriesCollection(1).Points(1).Select
    With Selection.Format.Fill
     .Visible = msoTrue
     .ForeColor.RGB = RGB(255, 0, 0)
     .Transparency = 0
     .Solid
    End With
    ActiveChart.SeriesCollection(1).Points(2).Select
    With Selection.Format.Fill
     .Visible = msoTrue
     .ForeColor.RGB = RGB(0, 255, 0)
     .Transparency = 0
     .Solid
    End With
    ActiveChart.SeriesCollection(1).Points(3).Select
    With Selection.Format.Fill
     .Visible = msoTrue
     .ForeColor.RGB = RGB(0, 0, 255)
     .Transparency = 0
     .Solid
    End With

 

[waller99]

    ActiveSheet.Shapes.AddChart.Select
    With ActiveChart
     .ChartType = xlPie
     .SetSourceData Source:=Range("Sheet1!$A$3:$B$5")
     .SeriesCollection(1).Select
     .SeriesCollection(1).Points(1).Select
    End With
    With Selection.Format.Fill
     .Visible = msoTrue
     .ForeColor.RGB = RGB(255, 0, 0)
     .Transparency = 0
     .Solid
    End With
    ActiveChart.SeriesCollection(1).Points(2).Select
    With Selection.Format.Fill
     .Visible = msoTrue
     .ForeColor.RGB = RGB(0, 255, 0)
     .Transparency = 0
     .Solid
    End With
    ActiveChart.SeriesCollection(1).Points(3).Select
    With Selection.Format.Fill
     .Visible = msoTrue
     .ForeColor.RGB = RGB(0, 0, 255)
     .Transparency = 0
     .Solid
    End With

I appreciate the response. The RGB values need to be variables because other rows will have a different RGB number. I need to create a bar chart and color each bar the RGB value in column "C". So for each bar in the chart, use the corresponding RGB value. Kind of hard to explain.

 

[ji32m]

    nR01 = Sheets("Sheet1").Range("C3").Value
    nG01 = Sheets("Sheet1").Range("D3").Value
    nB01 = Sheets("Sheet1").Range("E3").Value
    nR02 = Sheets("Sheet1").Range("C4").Value
    nG02 = Sheets("Sheet1").Range("D4").Value
    nB02 = Sheets("Sheet1").Range("E4").Value
    nR03 = Sheets("Sheet1").Range("C5").Value
    nG03 = Sheets("Sheet1").Range("D5").Value
    nB03 = Sheets("Sheet1").Range("E5").Value
    
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlColumnClustered
    Range("A3:B5").Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Parent.Delete
    ActiveSheet.Shapes.AddChart.Select
    With ActiveChart
     .ChartType = xlColumnClustered
     .SetSourceData Source:=Range("Sheet1!$A$3:$B$5")
     .SeriesCollection(1).Select
    End With
    
    ActiveChart.SeriesCollection(1).Points(1).Select
    With Selection.Format.Fill
     .Visible = msoTrue
     .ForeColor.RGB = RGB(nR01, nG01, nB01)
     .Transparency = 0
     .Solid
    End With
    ActiveChart.SeriesCollection(1).Points(2).Select
    With Selection.Format.Fill
     .Visible = msoTrue
     .ForeColor.RGB = RGB(nR02, nG02, nB02)
     .Transparency = 0
     .Solid
    End With
    ActiveChart.SeriesCollection(1).Points(3).Select
    With Selection.Format.Fill
     .Visible = msoTrue
     .ForeColor.RGB = RGB(nR03, nG03, nB03)
     .Transparency = 0
     .Solid
    End With

 

[ji32m]

On the statements

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlColumnClustered
    Range("A3:B5").Select
    ActiveSheet.ChartObjects("Chart 1").Activate

you could already have a chart there on the page, so just use the last two of four lines (Range... and ActiveSheet...), and name the chart from "Chart 1" to whatever yours is called.

 

[ji32m]

And if you absolutely have to have the color vals in "255,255,255" format in a single cell, look at the Split() function, operate on the comma delimiter and break out the 3 numbers into 3 separate variables for each bar.

 

[waller99]

I have broken the RGB values out into a separate cells.

COMPANY | TOTAL | R | G | B
company1 | 10 | 255 | 000 | 000
company2 | 20 | 000 | 255 | 000
company3 | 30 | 000 | 000 | 255
...
and so on...

 

[waller99]

This issue has been resolved. Thanks.

Assigning Bar Charts Different Colors based on RGB Numbers in Table with VBA Macro