VBA Chart Project - Color a Bar based on Cell Color (Code Included)
***Get the Downloadable Workbook here
https://chrisjterrell.com/blog/223074...
**Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-v...
This video is number two in a series where we are learning how to color a bar in a chart based on the background color of a cell. Specifically, we set the color of a chart bar using the background color (or interior.color) of cell A1.
The first thing that we do as we declare a chart object. Then we activate the first chart on the sheet and set the chart object equal to the activesheet. We add a select statement so you can see which cell we are using to get the pulling the background color. Then we add a message box that shows you what that interior color number. In VBA, the background color if found suing interior.color.
In researching this project, we found that coloring a chart bar required converting the color number into RGB. After doing some Googling, if found the way to pull RGG from a color number using a MOD formula. The RGB section uses the Mod function to pass in the appropriate colors to the variables r, g, and b.
Once we had the RGB colors, we needed to find the syntax for changing the bar's color. Then we were able to pass change the bar's color to the interior color of cell A1.
'CODE
=======================
Sub ColorAnItem()
Dim chrt As Chart
Sheet1.ChartObjects(1).Activate
Set chrt = ActiveChart
'We grab the color of the A1 on sheet 1
' then convert the color to RGB which is required for charts
Sheet1.Cells(1, 1).Select
MsgBox "We are selecting the color from Cells(1,1) which is " & Sheet1.Cells(1, 1).Interior.Color
clr = Sheet1.Cells(1, 1).Interior.Color
r = clr Mod 256
g = clr \ 256 Mod 256
b = clr \ 65536 Mod 256
chrt.SeriesCollection(1).Points(1).Format.Fill.ForeColor.RGB = RGB(r, g, b)
End Sub
