Hi guys,
So, I have found this formula that automatically changes chart colours depending on the original data cell colour. However, I am facing a problem when the original data cell colour is set to "no fill" the chart bar still comes out as a solid opeque "white". In my intended use, it is blocking my underlying text as I have made the chart box "transparent", but this bar is appearing as a opeque white block.
How do I modify this formula to ensure a "no fill" original cell colour is reflected in the chart accordingly? Thank you, appreciate the assistance!
I would like to create a semi doughnut chart as the attached. Without the bottom 180 degrees set as "no fill" it will block my underlying text.
This is the formula I found online:
Sub ColourDoughnut()
Dim cht As ChartObject
Dim i As Integer
Dim vntValues As Variant
Dim s As String
Dim myseries As Series
For Each cht In ActiveSheet.ChartObjects
For Each myseries In cht.Chart.SeriesCollection
If myseries.ChartType <> xlDoughnut Then GoTo SkipNotDoughnut
s = Split(myseries.Formula, ",")(2)
vntValues = myseries.Values
For i = 1 To UBound(vntValues)
myseries.Points(i).Interior.Color = Range(s).Cells(i).Interior.Color
Next i
SkipNotDoughnut:
Next myseries
Next cht
End Sub
So, I have found this formula that automatically changes chart colours depending on the original data cell colour. However, I am facing a problem when the original data cell colour is set to "no fill" the chart bar still comes out as a solid opeque "white". In my intended use, it is blocking my underlying text as I have made the chart box "transparent", but this bar is appearing as a opeque white block.
How do I modify this formula to ensure a "no fill" original cell colour is reflected in the chart accordingly? Thank you, appreciate the assistance!
I would like to create a semi doughnut chart as the attached. Without the bottom 180 degrees set as "no fill" it will block my underlying text.
This is the formula I found online:
Sub ColourDoughnut()
Dim cht As ChartObject
Dim i As Integer
Dim vntValues As Variant
Dim s As String
Dim myseries As Series
For Each cht In ActiveSheet.ChartObjects
For Each myseries In cht.Chart.SeriesCollection
If myseries.ChartType <> xlDoughnut Then GoTo SkipNotDoughnut
s = Split(myseries.Formula, ",")(2)
vntValues = myseries.Values
For i = 1 To UBound(vntValues)
myseries.Points(i).Interior.Color = Range(s).Cells(i).Interior.Color
Next i
SkipNotDoughnut:
Next myseries
Next cht
End Sub