Single Dynamic Graph Code

Question

In excel, I have 4 sets of properties that have rent data going horizontally, with the corresponding month of the rent data going horizontally at the top. The properties are listed going down vertically.

           June,'15  July,'15
Set 1                   
prop 1    $2,588.00     $2,588.00
prop 2    $1,835.00     $1,829.00   

Set 2                       
prop 3    $2,647.00     $2,707.00
prop 4    $2,501.00     $2,526.00

I want to create a VBA code with for a single graph that chooses the data from either property Set 1 or Set 2 to add to the graph. This is dependent on another cell (drop-down) changing in Excel:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCells As Range
' The variable KeyCells contains the cells that will cause an alert when they are changed.
Set KeyCells = Range("D5")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        'This is where I would place the graph code
    End If

End Sub

What would be the code to achieve such a task - building a dynamically changing graph?

Answer 1

Private Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCells As Range
' The variable KeyCells contains the cells that will cause an alert when they are changed.
Set KeyCells = Range("D5")

If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then

If Range("D5") = "Tremont" Then
    Activesheet.Chartobjects("Single_Dynamic_Chart").FullSeriesCollection(1).XValues = Range(X_axis_values)
    Activesheet.Chartobjects("Single_Dynamic_Chart").FullSeriesCollection(1).Name = "Tremont"
    Activesheet.Chartobjects("Single_Dynamic_Chart").FullSeriesCollection(1).Values  = Range(Y_axis_values)


'If a bar graph,
with Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB= RGB(0,0,0)
.Transparency = 0
.Solid
End With          

 ElseIf Range("D5") = "Saybrook Pointe" Then
     Activesheet.Chartobjects("Single_Dynamic_Chart").FullSeriesCollection(1).XValues = Range(X_axis_values)
     Activesheet.Chartobjects("Single_Dynamic_Chart").FullSeriesCollection(1).Name = "Saybrook Pointe"
     Activesheet.Chartobjects("Single_Dynamic_Chart").FullSeriesCollection(1).Values  = Range(Y_axis_values)

'If a bar graph,
with Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB= RGB(0,0,0)
.Transparency = 0
.Solid
End With   



        ElseIf Range("D5") = "21 Fitzsimons" Then
            'Similarly like above cases, define the X-axis,the series name and the values.
        ElseIf Range("D5") = "Mezzo" Then
            'Similarly like above cases, define the X-axis,the series name and the values.
        End If

    End If

End Sub