VBA Chart Project - Change Chart based on Cell Background Color - Get Download File скачать в хорошем качестве

VBA Chart Project - Change Chart based on Cell Background Color - Get Download File

***Get the Downloadable Workbook here https://chrisjterrell.com/blog/223074... **Grab the Free VBA Quick Reference Guide https://www.chrisjterrell.com/excel-v... 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