VBA Change Line Color

Question

Simply wanting to change the line color of columns E and B , this is my code:

Sub HistoricalProdUnitAndDemand()
' Creates a line chart for the demand column

    Worksheets("DATA").Activate

    ' Doesn't work because it is looking for a Worksheet not a SHEET WTF
    For Each Ws In Sheets
        If Ws.Name = "Production VS Demand" Then
            Application.DisplayAlerts = False
            Sheets("Production VS Demand").Delete
            Application.DisplayAlerts = True
            Exit For
        End If
    Next

    Columns("A:A").Select
    Selection.NumberFormat = "[$-en-US]mmm-yy;@"
    ' This formats column A to be in the proper date format of mmm-yy

    Range("A:A,E:E,B:B").Select
    ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select
    ActiveChart.SetSourceData Source:=Range("A:A,E:E,B:B")
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Production VS Demand"
    ' Places line chart in a new worksheet called Production VS Demand
    ActiveChart.Axes(xlCategory).Select
    Selection.TickLabels.Orientation = 70
    Selection.MajorTickMark = xlNone
    ActiveChart.ChartTitle.Select
    ActiveChart.ChartTitle.Text = "Production VS Demand"
    ' Adds in the chart's title
    ActiveChart.SetElement (msoElementLegendRight)
    ' Adds in the chart's legend to the right
    Selection.Format.TextFrame2.TextRange.Characters.Text = "Production VS Demand"
    With Selection.Format.TextFrame2.TextRange.Characters(1, 20).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
    End With
    ActiveChart.ChartArea.Select
    End Sub

Until we know what your question is, [this should prove useful](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — cybernetic.nomad, Feb 11 '20 at 21:03
That's why you need to strip out everything that's unrelated. Or just record a macro while doing *that specific step.* — BigBen, Feb 11 '20 at 21:09

score 0 · Accepted Answer · answered Feb 12 '20 at 08:09

You can create that :

Sub setColor(HexColors As Long)
  Range("E").Interior.Color = HexColors 
  Range("B").Interior.Color = HexColors
end sub

Then you can place a line in your Sub HistoricalProdUnitAndDemand() like below :

HistoricalProdUnitAndDemand()
  [...]
  setColor 49407
  [...]
end sub

You can look on the internet to find out your Hexadecimal colors (49407 is for a "Golden yellow" or something like that)

Bye & have a nice day,

VBA Change Line Color

1 Answers1