Excel – Changing bar colors in bar graph

bar-chartcolorsexcelgraphvba

I've created a VBA for Excel 2007 program that automatically creates bar graphs for ROI based on up to 52 different tabs in the active workbook. I'm close to done, and the only thing I cannot figure out is how to change the colors of the bargraphs.

The graphs are created in their own subfunction, called with a call like so. Every variable changes around whenever it's called.

Call AddChartObject(1, 1, "Example", extraWeeks, weekDifference)

My sub that it calls looks like this.

Sub AddChartObject(j As Integer, k As Integer, passedChartTitle As String, xtraWks As Integer, ttlWks As Integer)

    Dim topOfChart As Integer

    topOfChart = 25 + (350 * j)

    'Adds bar chart for total sales

    With ActiveSheet.ChartObjects.Add(Left:=375, Width:=475, Top:=topOfChart, Height:=325)
        .Chart.SetSourceData Source:=Sheets("Consolidation").Range("$A$" & 3 + ((17 + xtraWks) _
            * j) & ":$C$" & (4 + ttlWks) + ((17 + xtraWks) * k))
        .Chart.ChartType = xl3DColumnClustered
        .Chart.SetElement (msoElementDataLabelShow)
        .Chart.HasTitle = True
        .Chart.ChartTitle.Text = passedChartTitle & " Sales"
        .Chart.SetElement (msoElementLegendBottom)
        .Chart.SetElement (msoElementDataLabelNone)
        .Chart.RightAngleAxes = True
    End With

End Sub

The RGB color I want to use on the SECOND series in the bar chart is (155, 187, 89), per marketing's wishes. I'm pretty sure there is a .chart.????.???? = RGB (155, 187, 89) command I can use in my With to set this, but I have spent far too much time trying to figure it out, only to come up with nothing.

Best Answer

Have you tried

.Chart.SeriesCollection([index]).Interior.Color = RGB(155, 187, 89)

(where [index] is a placeholder for the series you want to change the color for)?

Code:

// Version 4.0
const pSBC=(p,c0,c1,l)=>{
    let r,g,b,P,f,t,h,i=parseInt,m=Math.round,a=typeof(c1)=="string";
    if(typeof(p)!="number"||p<-1||p>1||typeof(c0)!="string"||(c0[0]!='r'&&c0[0]!='#')||(c1&&!a))return null;
    if(!this.pSBCr)this.pSBCr=(d)=>{
        let n=d.length,x={};
        if(n>9){
            [r,g,b,a]=d=d.split(","),n=d.length;
            if(n<3||n>4)return null;
            x.r=i(r[3]=="a"?r.slice(5):r.slice(4)),x.g=i(g),x.b=i(b),x.a=a?parseFloat(a):-1
        }else{
            if(n==8||n==6||n<4)return null;
            if(n<6)d="#"+d[1]+d[1]+d[2]+d[2]+d[3]+d[3]+(n>4?d[4]+d[4]:"");
            d=i(d.slice(1),16);
            if(n==9||n==5)x.r=d>>24&255,x.g=d>>16&255,x.b=d>>8&255,x.a=m((d&255)/0.255)/1000;
            else x.r=d>>16,x.g=d>>8&255,x.b=d&255,x.a=-1
        }return x};
    h=c0.length>9,h=a?c1.length>9?true:c1=="c"?!h:false:h,f=this.pSBCr(c0),P=p<0,t=c1&&c1!="c"?this.pSBCr(c1):P?{r:0,g:0,b:0,a:-1}:{r:255,g:255,b:255,a:-1},p=P?p*-1:p,P=1-p;
    if(!f||!t)return null;
    if(l)r=m(P*f.r+p*t.r),g=m(P*f.g+p*t.g),b=m(P*f.b+p*t.b);
    else r=m((P*f.r**2+p*t.r**2)**0.5),g=m((P*f.g**2+p*t.g**2)**0.5),b=m((P*f.b**2+p*t.b**2)**0.5);
    a=f.a,t=t.a,f=a>=0||t>=0,a=f?a<0?t:t<0?a:a*P+t*p:0;
    if(h)return"rgb"+(f?"a(":"(")+r+","+g+","+b+(f?","+m(a*1000)/1000:"")+")";
    else return"#"+(4294967296+r*16777216+g*65536+b*256+(f?m(a*255):0)).toString(16).slice(1,f?undefined:-2)
}

Usage:

// Setup:

let color1 = "rgb(20,60,200)";
let color2 = "rgba(20,60,200,0.67423)";
let color3 = "#67DAF0";
let color4 = "#5567DAF0";
let color5 = "#F3A";
let color6 = "#F3A9";
let color7 = "rgb(200,60,20)";
let color8 = "rgba(200,60,20,0.98631)";

// Tests:

/*** Log Blending ***/
// Shade (Lighten or Darken)
pSBC ( 0.42, color1 ); // rgb(20,60,200) + [42% Lighter] => rgb(166,171,225)
pSBC ( -0.4, color5 ); // #F3A + [40% Darker] => #c62884
pSBC ( 0.42, color8 ); // rgba(200,60,20,0.98631) + [42% Lighter] => rgba(225,171,166,0.98631)

// Shade with Conversion (use "c" as your "to" color)
pSBC ( 0.42, color2, "c" ); // rgba(20,60,200,0.67423) + [42% Lighter] + [Convert] => #a6abe1ac

// RGB2Hex & Hex2RGB Conversion Only (set percentage to zero)
pSBC ( 0, color6, "c" ); // #F3A9 + [Convert] => rgba(255,51,170,0.6)

// Blending
pSBC ( -0.5, color2, color8 ); // rgba(20,60,200,0.67423) + rgba(200,60,20,0.98631) + [50% Blend] => rgba(142,60,142,0.83)
pSBC ( 0.7, color2, color7 ); // rgba(20,60,200,0.67423) + rgb(200,60,20) + [70% Blend] => rgba(168,60,111,0.67423)
pSBC ( 0.25, color3, color7 ); // #67DAF0 + rgb(200,60,20) + [25% Blend] => rgb(134,191,208)
pSBC ( 0.75, color7, color3 ); // rgb(200,60,20) + #67DAF0 + [75% Blend] => #86bfd0

/*** Linear Blending ***/
// Shade (Lighten or Darken)
pSBC ( 0.42, color1, false, true ); // rgb(20,60,200) + [42% Lighter] => rgb(119,142,223)
pSBC ( -0.4, color5, false, true ); // #F3A + [40% Darker] => #991f66
pSBC ( 0.42, color8, false, true ); // rgba(200,60,20,0.98631) + [42% Lighter] => rgba(223,142,119,0.98631)

// Shade with Conversion (use "c" as your "to" color)
pSBC ( 0.42, color2, "c", true ); // rgba(20,60,200,0.67423) + [42% Lighter] + [Convert] => #778edfac

// RGB2Hex & Hex2RGB Conversion Only (set percentage to zero)
pSBC ( 0, color6, "c", true ); // #F3A9 + [Convert] => rgba(255,51,170,0.6)

// Blending
pSBC ( -0.5, color2, color8, true ); // rgba(20,60,200,0.67423) + rgba(200,60,20,0.98631) + [50% Blend] => rgba(110,60,110,0.83)
pSBC ( 0.7, color2, color7, true ); // rgba(20,60,200,0.67423) + rgb(200,60,20) + [70% Blend] => rgba(146,60,74,0.67423)
pSBC ( 0.25, color3, color7, true ); // #67DAF0 + rgb(200,60,20) + [25% Blend] => rgb(127,179,185)
pSBC ( 0.75, color7, color3, true ); // rgb(200,60,20) + #67DAF0 + [75% Blend] => #7fb3b9

/*** Other Stuff ***/
// Error Checking
pSBC ( 0.42, "#FFBAA" ); // #FFBAA + [42% Lighter] => null  (Invalid Input Color)
pSBC ( 42, color1, color5 ); // rgb(20,60,200) + #F3A + [4200% Blend] => null  (Invalid Percentage Range)
pSBC ( 0.42, {} ); // [object Object] + [42% Lighter] => null  (Strings Only for Color)
pSBC ( "42", color1 ); // rgb(20,60,200) + ["42"] => null  (Numbers Only for Percentage)
pSBC ( 0.42, "salt" ); // salt + [42% Lighter] => null  (A Little Salt is No Good...)

// Error Check Fails (Some Errors are not Caught)
pSBC ( 0.42, "#salt" ); // #salt + [42% Lighter] => #a5a5a500  (...and a Pound of Salt is Jibberish)

// Ripping
pSBCr ( color4 ); // #5567DAF0 + [Rip] => [object Object] => {'r':85,'g':103,'b':218,'a':0.941}

The picture below will help show the difference in the two blending methods:

Micro Functions

If you really want speed and size, you will have to use RGB not HEX. RGB is more straightforward and simple, HEX writes too slow and comes in too many flavors for a simple two-liner (IE. it could be a 3, 4, 6, or 8 digit HEX code). You will also need to sacrifice some features, no error checking, no HEX2RGB nor RGB2HEX. As well, you will need to choose a specific function (based on its function name below) for the color blending math, and if you want shading or blending. These functions do support alpha channels. And when both input colors have alphas it will Linear Blend them. If only one of the two colors has an alpha, it will pass it straight thru to the resulting color. Below are two liner functions that are incredibly fast and small:

const RGB_Linear_Blend=(p,c0,c1)=>{
    var i=parseInt,r=Math.round,P=1-p,[a,b,c,d]=c0.split(","),[e,f,g,h]=c1.split(","),x=d||h,j=x?","+(!d?h:!h?d:r((parseFloat(d)*P+parseFloat(h)*p)*1000)/1000+")"):")";
    return"rgb"+(x?"a(":"(")+r(i(a[3]=="a"?a.slice(5):a.slice(4))*P+i(e[3]=="a"?e.slice(5):e.slice(4))*p)+","+r(i(b)*P+i(f)*p)+","+r(i(c)*P+i(g)*p)+j;
}

const RGB_Linear_Shade=(p,c)=>{
    var i=parseInt,r=Math.round,[a,b,c,d]=c.split(","),P=p<0,t=P?0:255*p,P=P?1+p:1-p;
    return"rgb"+(d?"a(":"(")+r(i(a[3]=="a"?a.slice(5):a.slice(4))*P+t)+","+r(i(b)*P+t)+","+r(i(c)*P+t)+(d?","+d:")");
}

const RGB_Log_Blend=(p,c0,c1)=>{
    var i=parseInt,r=Math.round,P=1-p,[a,b,c,d]=c0.split(","),[e,f,g,h]=c1.split(","),x=d||h,j=x?","+(!d?h:!h?d:r((parseFloat(d)*P+parseFloat(h)*p)*1000)/1000+")"):")";
    return"rgb"+(x?"a(":"(")+r((P*i(a[3]=="a"?a.slice(5):a.slice(4))**2+p*i(e[3]=="a"?e.slice(5):e.slice(4))**2)**0.5)+","+r((P*i(b)**2+p*i(f)**2)**0.5)+","+r((P*i(c)**2+p*i(g)**2)**0.5)+j;
}

const RGB_Log_Shade=(p,c)=>{
    var i=parseInt,r=Math.round,[a,b,c,d]=c.split(","),P=p<0,t=P?0:p*255**2,P=P?1+p:1-p;
    return"rgb"+(d?"a(":"(")+r((P*i(a[3]=="a"?a.slice(5):a.slice(4))**2+t)**0.5)+","+r((P*i(b)**2+t)**0.5)+","+r((P*i(c)**2+t)**0.5)+(d?","+d:")");
}

Want more info? Read the full writeup on github.

(P.s. If anyone has the math for another blending method, please share.)

Python – Named colors in matplotlib

I constantly forget the names of the colors I want to use and keep coming back to this question =)

The previous answers are great, but I find it a bit difficult to get an overview of the available colors from the posted image. I prefer the colors to be grouped with similar colors, so I slightly tweaked the matplotlib answer that was mentioned in a comment above to get a color list sorted in columns. The order is not identical to how I would sort by eye, but I think it gives a good overview.

I updated the image and code to reflect that 'rebeccapurple' has been added and the three sage colors have been moved under the 'xkcd:' prefix since I posted this answer originally.

I really didn't change much from the matplotlib example, but here is the code for completeness.

import matplotlib.pyplot as plt
from matplotlib import colors as mcolors


colors = dict(mcolors.BASE_COLORS, **mcolors.CSS4_COLORS)

# Sort colors by hue, saturation, value and name.
by_hsv = sorted((tuple(mcolors.rgb_to_hsv(mcolors.to_rgba(color)[:3])), name)
                for name, color in colors.items())
sorted_names = [name for hsv, name in by_hsv]

n = len(sorted_names)
ncols = 4
nrows = n // ncols

fig, ax = plt.subplots(figsize=(12, 10))

# Get height and width
X, Y = fig.get_dpi() * fig.get_size_inches()
h = Y / (nrows + 1)
w = X / ncols

for i, name in enumerate(sorted_names):
    row = i % nrows
    col = i // nrows
    y = Y - (row * h) - h

    xi_line = w * (col + 0.05)
    xf_line = w * (col + 0.25)
    xi_text = w * (col + 0.3)

    ax.text(xi_text, y, name, fontsize=(h * 0.8),
            horizontalalignment='left',
            verticalalignment='center')

    ax.hlines(y + h * 0.1, xi_line, xf_line,
              color=colors[name], linewidth=(h * 0.8))

ax.set_xlim(0, X)
ax.set_ylim(0, Y)
ax.set_axis_off()

fig.subplots_adjust(left=0, right=1,
                    top=1, bottom=0,
                    hspace=0, wspace=0)
plt.show()

Additional named colors

Updated 2017-10-25. I merged my previous updates into this section.

xkcd

If you would like to use additional named colors when plotting with matplotlib, you can use the xkcd crowdsourced color names, via the 'xkcd:' prefix:

plt.plot([1,2], lw=4, c='xkcd:baby poop green')

Now you have access to a plethora of named colors!

Tableau

The default Tableau colors are available in matplotlib via the 'tab:' prefix:

plt.plot([1,2], lw=4, c='tab:green')

There are ten distinct colors:

HTML

You can also plot colors by their HTML hex code:

plt.plot([1,2], lw=4, c='#8f9805')

This is more similar to specifying and RGB tuple rather than a named color (apart from the fact that the hex code is passed as a string), and I will not include an image of the 16 million colors you can choose from...

For more details, please refer to the matplotlib colors documentation and the source file specifying the available colors, _color_data.py.