Excel Vba Change Bar Chart Color Based On Value
Bar charts are a great way to visualize data in Microsoft Excel, but sometimes you want to add a little more pizzazz to your charts. One way to do this is by changing the color of the bars based on their value. In this article, we'll show you how to use VBA (Visual Basic for Applications) to accomplish this.
Step 1: Create a Bar Chart
The first step is to create a bar chart in Excel. To do this, select the data you want to use for the chart and click on the "Insert" tab. From there, select "Bar Chart" and choose the type of chart you want to create.
Step 2: Open the Visual Basic Editor
Next, you'll need to open the Visual Basic Editor. To do this, press "Alt" + "F11" on your keyboard. This will open the editor, where you can write and edit VBA code.
Step 3: Write the VBA Code
Now it's time to write the VBA code that will change the color of the bars based on their value. Here's an example of what the code might look like:
Sub ChangeBarColor()Dim cht As ChartDim ser As SeriesDim i As LongSet cht = ActiveSheet.ChartObjects(1).ChartSet ser = cht.SeriesCollection(1)For i = 1 To ser.Points.CountSelect Case ser.Values(i)Case Is >= 80ser.Points(i).Format.Fill.ForeColor.RGB = RGB(0, 176, 80)Case Is >= 60ser.Points(i).Format.Fill.ForeColor.RGB = RGB(255, 192, 0)Case Elseser.Points(i).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)End SelectNext iEnd Sub
In this example, we're using a Select Case statement to determine what color to make each bar based on its value. If the value is greater than or equal to 80, we'll make it green. If the value is between 60 and 79, we'll make it yellow. And if the value is less than 60, we'll make it red.
Step 4: Run the Code
Once you've written the code, it's time to run it. To do this, switch back to your Excel worksheet and select the chart you want to change the colors for. Then go back to the Visual Basic Editor and click on the "Run" button (or press "F5" on your keyboard).
Conclusion
And that's it! With just a few lines of VBA code, you can change the color of your bar charts based on their value. This can be a great way to make your charts more visually appealing and easier to read. So why not give it a try?