Dynamic Excel Chart: Pulling Data from Two Different Tables (VBA?)

you really dont need VBA to achieve what you are asking but as you asked for a VBA specific response

heres my code and attached file
i deleted all your other charts and just left one and named it chart 1

the title is based on cell o6 so thats why it isnt in the code

Sub DynamicChart()
    
    Select Case Range("O4")
    
    Case Is = 1
        'maximum value
        ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(1).Values = Range("L14:P14")
        ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(0, 0, 255) 'RGB for blue
    
    Case Else
        'everything else
        ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(1).Values = Range(Range("a12").Offset(Range("o4"), 3), Range("a12").Offset(Range("o4"), 7))
        ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(255, 102, 0) 'RGB for orange
    
    End Select
    
End Sub