CODEWITHSUNDEEP
excelvba
Elmir Akbarov
Elmir Akbarov

Reputation: 23

VBA to format border of Chart bar from corresponding cell

I use following VBA to format Chart bar color from corresponding cell. It works fine. But now I need to add 1 more line, to format Chart bar's border as cell's border. I can't figure out how to do this. Can anybody help me please?

Sub ColorOfChart1()
    
   
    Dim i As Long, j As Long, k As Long
    Dim ws As Worksheet, cht As Chart, c As Range, b As Border
    
    Set ws = ActiveSheet
    k = 0
    For i = 1 To 54
        Set cht = ws.ChartObjects("Cluster" & i).Chart
        For j = 1 To cht.SeriesCollection.Count
            Set c = ws.Cells(68, j + 5 + k)
            Set b = c.Borders(xlEdgeBottom) 'Just using bottom border, in case
                                            ' not all borders are consistent..
            With cht.SeriesCollection(j)
                .Format.Fill.ForeColor.RGB = c.Interior.Color
                .Format.Line.Weight = 0.5
                .Border.LineStyle = b.LineStyle '<< copy each border property over
                .Border.Color = b.Color
            End With
        Next j
        k = k + 10
    Next i
End Sub

Upvotes: 0

Views: 391

Answers (1)

Tim Williams
Tim Williams

Reputation: 166585

Something like this should be close:

Sub ColorOfChart()
    
    Dim i As Long, j As Long, k As Long
    Dim ws As Worksheet, cht As Chart, c As Range, b As Border
    
    Set ws = ActiveSheet
    k = 0
    For i = 1 To 54
        Set cht = ws.ChartObjects("Cluster" & i).Chart
        For j = 1 To cht.SeriesCollection.Count
            Set c = ws.Cells(68, j + 5 + k)
            Set b = c.Borders(xlEdgeBottom) 'Just using bottom border, in case
                                            ' not all borders are consistent..
            With cht.SeriesCollection(j)
                .Format.Fill.ForeColor.RGB = c.Interior.Color
                .Format.line.Weight = 0.5
                .Border.LineStyle = b.LineStyle '<< copy each border property over
                .Border.Color = b.Color
            End With
        Next j
        k = k + 10
    Next i
End Sub

Note you don't need the RGB breakdown for the ForeColor - that will take a Long

Upvotes: 1

Related Questions