VBA Chart Project - Color Single Bar from Cells in a Range

Опубликовано: 17 Март 2025
на канале: EverydayVBA

1,472

This video is the fourth in the chart color series, where we change chart colors based on the interior color of a cell. This video walks through changing the first chart bar to the interior color for all the cells in column A. We use a For Loop to loop through the range in column A and change the color on our chart's first bar.

For this macro, we use three objects, a chartobject, chart, and a range object. First, we set our chartobject and our chart. For our range object, we use the currentregion to define the range in column A. Once we have identified the range, we loop through each cell within the range with a For Loop. Within the loop, we have a select statement that helps identify what cell the loop is on. Use this as a guide if you are newer to VBA. As we iterate through our range, we use clr as our color variable and make it equal to the interior.color of the cell. We again use the MOD function to get RGB and use our three variables r, g, b.

To simplify the code, we hard code our color change the first bar in the first chart (chrt.SeriesCollection(1).Points(1).Format.Fill.ForeColor.RGB). We did this to make learning the VBA easier. In future videos, we will use variables to make this more dynamic.

The last thing that we do within our loop is to add a message box that displays the RGB colors we used. The message box allows us to see the color change through each iteration through the range. As we run the code, you can see that the first bar changes to every color, and then a message box will display the RGB numbers for the code.

'============================
'CODE
'============================

Sub ScratchColorTable()

Dim cht As ChartObject
Dim chrt As Chart
Dim Rng As Range

Set cht = Sheet1.ChartObjects(1)
Set chrt = cht.Chart

Set Rng = Sheet1.Cells(1, 1).CurrentRegion

For Each cll In Rng
Rng.Select
clr = cll.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)
cht.Activate
MsgBox "Color for Selected Cells is R-" & r & " G-" & g & " B-" & b
Next

End Sub