***Get the Downloadable Workbook here
chrisjterrell....
**Grab the Free VBA Quick Reference Guide
www.chrisjterr...
This chart project started simple enough. The goal was to change a chart's color based on the background color (or interior.color) of a cell. After doing a little research, we found out that this was a lot more difficult than expected. We had to Loop through the sheets, the chartobjects, the charts, the FullSeriesCollections, and all of the individual series. We used a For Loop, an array, and an If statement to compare the chart's value to the range value. If it was an exact match, we grabbed the cell's interior color and used a fancy mod formula to turn the color number into RGB format for the Chart (Thank you, Internet).
In the first video of the series, we looked at how the chart object. We did some digging using the locals and found out how where the axis label lived. Recording a macro was helpful along the way. In the second video, we wrote code that allowed us to change the chart's first bar. In the next video, we used a loop to change all the bars to a single color. We needed to do to make sure we had a default color for the chart.
The fourth video was code for changing the first bar to the color that was in the range from A1 to A5. After this step, we would need to compare the Axis value and the Range value with an if statement, and we would be very close to completion.
The fifth and sixth video in the series were loops that allowed us to loop through all the sheets, chartobjects, charts, fullSeriesCollections, and series within the entire workbook. Before this point, we were working on single charts.
In this video, we walk through all the code and explain why we coded this workbook the way we did. This project was fun, and I hope you think so as well.
If you like this video, please subscribe or comment below.
'===========================
'CODE - This will error out unless you have charts in your workbook. It will be easier to go to the blog link above download the file :)
'===========================
Sub ChartColor()
Dim sht As Worksheet
Dim cht As ChartObject
Dim chrt As Chart
Dim fs As FullSeriesCollection
Dim Rng As Range
Set Rng = Sheet1.Cells(1, 1).CurrentRegion
'Loop through all worksheets Module eLoopSheetAndCharts
For Each sht In Worksheets
For Each cht In sht.ChartObjects
'Loop through the FullSeriesCollections Module fLoopSeriesCollection
For c = 1 To cht.Chart.FullSeriesCollection.Count
cht.Activate
Set chrt = ActiveChart
Set fs = chrt.FullSeriesCollection
'Color All Item Module bColorAllItems
For Each itm In fs
'Grab the Axis Valuse and put them in a array for the for loop
oarray = itm.XValues
For rw = 1 To UBound(oarray)
clr = Sheet1.Cells(1, 1).Interior.Color
r = clr Mod 256
g = clr \ 256 Mod 256
b = clr \ 65536 Mod 256
chrt.SeriesCollection(c).Points(rw).Format.Fill.ForeColor.RGB = RGB(r, g, b)
'Color an Item Module bColorAnItem
For Each cll In Rng
If oarray(rw) = cll Then
clr = cll.Interior.Color
r = clr Mod 256
g = clr \ 256 Mod 256
b = clr \ 65536 Mod 256
chrt.SeriesCollection(c).Points(rw).Format.Fill.ForeColor.RGB = RGB(r, g, b)
End If
Next
Next
Next
Next
Next
Next
End Sub
23 сен 2024
