Bar Chart inverted color

[Av8tordude]

how can i make the bars colored (red for negative numbers and green for positive numbers)

Sub UpdateChartScrollbar(scrollbarValue As Long)
    Dim i As Long, MaxIndex As Long, LRow As Long
    Dim FName As String
    Dim Cht As Chart
    
    Set Wks = ActiveSheet
    Set Cht = Wks.ChartObjects("ch_Symbol").Chart
    
    LRow = Wks.Cells(Wks.Rows.Count, "BE").End(xlUp).row
    MaxIndex = (LRow - 7) \ 5
    ChartScrollbar.Max = MaxIndex
    
    For i = 0 To MaxIndex
        If scrollbarValue = i Then
            With Cht.SeriesCollection(1)
                .XValues = Wks.Range("BE" & (i * 5) + 7 & ":BE" & (i * 5) + 11)
                .values = Wks.Range("BG" & (i * 5) + 7 & ":BG" & (i * 5) + 11)
            End With
            Exit For
        End If
    Next i
    
    lbChtPgs.Caption = scrollbarValue + 1 & " of " & MaxIndex + 1

    With Cht.SeriesCollection(1)
        .ApplyDataLabels
        .DataLabels.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(255, 255, 255)
        .DataLabels.Position = xlLabelPositionInsideBase
    End With

    Wks.Shapes("ch_Symbol").Visible = True
    FName = Application.DefaultFilePath & "\TempChart.gif"
    Cht.Export Filename:=FName, FilterName:="GIF"
    Image8.Picture = LoadPicture(FName)
    Kill FName
    'Wks.Shapes("ch_Symbol").Visible = False
End Sub

 

[bobaol]

hello, try this. cheers!

''Make sure to replace "A1:B10" with the actual range of your data
Sub ColorBarChart()
    Dim rngData As Range
    Dim chtChart As ChartObject
    Dim serSeries As Series
    Dim i As Integer

    ' Define the range of data for the chart
    Set rngData = Sheet1.Range("A1:B10") ' Update the range as per your data
    
    ' Add a new chart
    Set chtChart = Sheet1.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225)
    
    ' Specify the chart type
'    chtChart.Chart.ChartType = xlColumnClustered
    chtChart.Chart.ChartType = xlBarClustered
    
    ' Add data to the chart
    With chtChart.Chart
        .SetSourceData Source:=rngData
    End With
    
    ' Loop through each series in the chart
    For Each serSeries In chtChart.Chart.SeriesCollection
        ' Loop through each point in the series
        For i = 1 To serSeries.Points.Count
            ' Check if the value is negative
            If serSeries.Values(i) < 0 Then
                ' Set the bar color to red for negative values
                serSeries.Points(i).Format.Fill.ForeColor.RGB = RGB(255, 0, 0) ' Red
            Else
                ' Set the bar color to green for positive values
                serSeries.Points(i).Format.Fill.ForeColor.RGB = RGB(0, 255, 0) ' Green
            End If
        Next i
    Next serSeries
End Sub