Change/Preserve the color of bars in Excel Graph

Every week I prepare one report in Excel with lot of graphs. The data in the excel sheet is added in every report and the graphs that are generated based on that data are of course also updated. The client was satisfied with the report but asked me to preserve the color of the various countries that appear on the stacked bar chart. Initially for couple of weeks I didn't really paid much attention but then the client got little demanding and requested me again in harsh tone to do it. I still use Excel 2003 and not sure whether this feature is there in 2007 version, I guess not. After searching on the internet I did found a solution that requires creation of a macro that can be used to hard code the values and assign them specific color.

Here is the code. I hope it will be helpful to you. Enjoy.

  1. Sub change_bar_color()
  2.  
  3.  
  4.     NumPoints = ActiveChart.SeriesCollection.Count
  5.        
  6.     For x = 1 To NumPoints
  7.  
  8.         thispt = ActiveChart.SeriesCollection(x).Name      
  9.        
  10.         Select Case thispt
  11.             Case "India"
  12.                 ActiveChart.SeriesCollection(x).Interior.ColorIndex = 2
  13.             Case "USA"
  14.                 ActiveChart.SeriesCollection(x).Interior.ColorIndex = 14
  15.             Case "Africa"
  16.                 ActiveChart.SeriesCollection(x).Interior.ColorIndex = 44
  17.             Case "Australia"
  18.                 ActiveChart.SeriesCollection(x).Interior.ColorIndex = 5
  19.             Case "Other"
  20.                 ActiveChart.SeriesCollection(x).Interior.ColorIndex = 21
  21.                    
  22.             Case Else
  23.                 ' Add code here to handle an unexpected label
  24.         End Select
  25.                  
  26.     Next x
  27.  
  28.  
  29. End Sub

Of course you need to modify the above code as per your needs. Though I am a hard core linux user but for few things I still use windows xp and Excel is one tool that I believe is one of the best and most powerful tool built by Microsoft. Lot of amazing things can be done in Excel and its power can be extended with the help of VBA to create applications and automating.

Preserve colors in Excel

Add new comment

Want to contact me?

Life begins outside your comfort zone and that is where your courage is developed". Write to me by filling this form. For living I am an Atlassian Consultant who love both Jira and Drupal. I am the author of "Mastering JIRA" and "Mastering JIRA 7" books. I Love both JIRA and Drupal (a bit more). You can Write To Me and I will get back to you as soon as I can. Have a nice day!

Social Links

Subscribe to my mailing list.

Enter your email address: