Conditionally change colors of individual bars on a histogram in Excel VB based on a range
I am working on an Excel project and am trying to format the colors of a bar chart (and then a pie chart for the same reasons) to display RED
, GREEN
or YELLOW
based on a different range of data. Data range ...
Sheet :Overview
Range :E15:E36
These values are percentages. Depending on what percentage it is in between, I would like the bars to be formatted green, red, or yellow.
If between 100 - 90, green If between 89 - 70, Yellow If between 69 - 1, Red
Below is my code to this point (for a histogram):
Sub Macro2()
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Overview").Range("A15:A36")
ActiveChart.SetSourceData Source:=Sheets("Overview").Range("A15:A36,B15:B36")
ActiveChart.ApplyLayout (2)
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.Legend.Select
Selection.Delete
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.ChartTitle.Select
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.ChartTitle.Text = "Rating Site Distribution"
End Sub
Any help would be greatly appreciated! I am not quite familiar with VBA and totally feel out of my element on this ...
Also, would the same function work for a pie chart to determine color by the same parameters?
Thanks in advance!
source to share
here's the vba function i use to invert negative bars so they are red. Perhaps this can be adapted:
The function is called from a subroutine in module a in the workbook like this:
Sub FixTheGraph()
FormatGraph("Overview")
End Sub
Here's the function. You can just paste it under the subroutine:
Function FormatGraph(myWorksheet As String)
Excel.Sheets(myWorksheet).Select
Dim myChartObject As ChartObject
For Each myChartObject In Excel.ActiveSheet.ChartObjects
myChartObject.Select
Dim myPoint As Integer, valArray
With Excel.ActiveChart.SeriesCollection(1)
valArray = .Values
For myPoint = 1 To .Points.Count
'myVal = valArray(myPoint)
Select Case valArray(myPoint)
Case 0.9 To 1
With .Points(myPoint)
.Interior.ColorIndex = 1 '<change colour to suit
End With
Case 0.7 To 0.9
With .Points(myPoint)
.Interior.ColorIndex = 5 '<change colour to suit
End With
Case Is < 0.7
With .Points(myPoint)
.Interior.ColorIndex = 3
End With
End Select
Next
End With
Next myChartObject
Excel.Sheets(myWorksheet).Cells(1, 1).Select
End Function
source to share
What you want to do is the xlBarStacked chart type. Then give him three batches - one for each color.
In each series, if the element value is in the range, use the value, otherwise set the series.Value parameter to 0.
Since your ranges are mutually exclusive, each bar will only have one color.
source to share