VBA Chart Project - Color All Chart based on Cell Color (Code Included)
**Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-v...
Next Video: • VBA Chart Project - Color Single Bar ...
This video is the third video in our Color Chart series, where we go through and alter a chart based on the background colors of a range of cells. To do this, we use the interior color or background color of a cell. First, we declare an object as a chart, and then we set that object equal to the first chart in sheet1. Next, we grab the color from cell one or A1 on sheet1. The interior color of a cell is a long integer that we will convert to RGB.
We take that long integer, and we make a variable called clr equal to the interior color. Then we use the MOD function to convert the long number into its RGB and make variables r, g, and b equal to the appropriate 0 to 255 number. Next, we use a for loop to loop through all the points in the first series collection (chrt.SeriesCollection(1)). The Series collections represent the number of columns that the chart is graphing. In this example, only one of the charts has two Series Collections, the chart with both the vehicle count and the % top 15. Our for loop goes from the 1 to the count of the points in SeriesCollection(1). Every iteration of the loop, we changed the point to the same color background color in Cell A1. When the loop finishes, the entire chart will be the same color.
'========================
'CODE
'=========================
Sub ColorAllItems()
Dim chrt As Chart
Set chrt = Sheet1.ChartObjects(1).Chart
'We grab the color of the A1 on sheet 1
' then convert the color to RGB which is required for charts
clr = Sheet1.Cells(1, 1).Interior.Color
r = clr Mod 256
g = clr \ 256 Mod 256
b = clr \ 65536 Mod 256
'The loop below loops through the all Axis points
For c = 1 To chrt.SeriesCollection(1).Points.Count
chrt.SeriesCollection(1).Points(c).Format.Fill.ForeColor.RGB = RGB(r, g, b)
Next
End Sub
