Update each bar color based on values in column A

[anand3dinesh]

Hi All,

I would like to fill colours of each bar based on values available in column A using VBA. Please see below image.
I can simply fill the colour manually, but i would like to do using VBA because I am producing same kind of chart where more bars adds or removes so colours on bar graphs changed in this scenario.
so I would like to fix these colours automatically based on values in column A before publish.

 

[Herakles]

Call this procdure such as :

with the worksheet object, range object and chart name.

Call subColorSeriesCollectionPoint(ActiveSheet, ActiveSheet.Range("A1:A3"), "Chart 1")

Public Sub subColorSeriesCollectionPoint(Ws As Worksheet, rngColors As Range, strChartName As String)
Dim chrt As Chart
Dim lngColor As Long
Dim r As Long
Dim g As Long
Dim b As Long
Dim rng As Range
Dim intPoint As Integer

    Ws.ChartObjects(strChartName).Activate
        
    Set chrt = ActiveChart
                     
    Ws.Cells(1, 1).Select
    
    For Each rng In rngColors.Cells
    
        lngColor = rng.Interior.Color
    
        r = lngColor Mod 256
        g = lngColor \ 256 Mod 256
        b = lngColor \ 65536 Mod 256
        
        intPoint = intPoint + 1
                
        chrt.SeriesCollection(1).Points(intPoint).Format.Fill.ForeColor.RGB = RGB(r, g, b)
   
    Next rng
    
End Sub

 

[anand3dinesh]

Call this procdure such as :

with the worksheet object, range object and chart name.

Call subColorSeriesCollectionPoint(ActiveSheet, ActiveSheet.Range("A1:A3"), "Chart 1")

Public Sub subColorSeriesCollectionPoint(Ws As Worksheet, rngColors As Range, strChartName As String)
Dim chrt As Chart
Dim lngColor As Long
Dim r As Long
Dim g As Long
Dim b As Long
Dim rng As Range
Dim intPoint As Integer

    Ws.ChartObjects(strChartName).Activate
     
    Set chrt = ActiveChart
                  
    Ws.Cells(1, 1).Select
 
    For Each rng In rngColors.Cells
 
        lngColor = rng.Interior.Color
 
        r = lngColor Mod 256
        g = lngColor \ 256 Mod 256
        b = lngColor \ 65536 Mod 256
     
        intPoint = intPoint + 1
             
        chrt.SeriesCollection(1).Points(intPoint).Format.Fill.ForeColor.RGB = RGB(r, g, b)
 
    Next rng
 
End Sub

Thanks for your response it works exactly how I wanted but my actual graphs are come from power query which I connected form other excels.
I have legends colour references are in my working excel can this be compared with legends please?

example: If Legend="a" then make green
elseif Legend = "b" then make it amber etc?

I have other legends too but i just want to compare Legends, only when Chart legend is matches with values in column A then make that particular legend to colour i want to change

 

[Herakles]

Anything may be possible!!

I'm not a charts expert.

The legend comes from the Chart Data Range on the worksheet.

I assuming that by using Power Query you don't have a Chart Data Range on the worksheet.

Can you produce a list using Power Query of the legends for each chart and put this into the worksheet?

Where do you keep a list of the colours and what structure does this data have?

 

[anand3dinesh]

Anything may be possible!!

I'm not a charts expert.

The legend comes from the Chart Data Range on the worksheet.

I assuming that by using Power Query you don't have a Chart Data Range on the worksheet.

Can you produce a list using Power Query of the legends for each chart and put this into the worksheet?

Where do you keep a list of the colours and what structure does this data have?

I don't see any option to upload my excel can I share it via external link?