I am trying to create a Macro that updates the border of all of the bars in the charts of a given sheet, if the referenced data point the bar is linked to is an “Actual” or a “Consensus”. This information is given in the same row, a few cells to the left of the value referenced in the chart.

I’ve been struggling to make this work as it involves a degree of comfortability with Macros I don’t have yet, but my professor is looking for me to complete it. The majority of my work so far has revolved around more simple cell formatting Macros.

I tried recording what I wanted to do and inserting an If statement to make it work, however this ultimately did not yield results.

Sub ColorBarsBasedOnColumnK()

    Dim ws As Worksheet
    Dim chrtObj As ChartObject
    Dim ser As Series
    Dim i As Integer
    Dim j As Integer
    Dim lastRow As Long
    Dim cellValue As String

    Set ws = ActiveSheet   

    For Each chrtObj In ws.ChartObjects

        Set ser = chrtObj.Chart.SeriesCollection(1)
        For i = 1 To ser.Points.Count

            lastRow = ws.Cells(ws.Rows.Count, ser.XValues(1).Column).End(xlUp).Row           

            If i <= lastRow Then

                cellValue = ws.Cells(i + 1, 11).Value
                If cellValue = "E" Then
                    ser.Points(i).Format.Fill.ForeColor.RGB = RGB(255, 0, 0) ' Red color, adjust as needed
                Else
                    ser.Points(i).Format.Fill.ForeColor.RGB = RGB(0, 0, 255) ' Blue color, adjust as needed
                End If
            End If
        Next i
    Next chrtObj
End Sub