Trying to set colorindex of chart Error 438

[vba317]

After reviewing FAQ707-4811 and researching the issue I cant seem to be able to pick the correct object to change the color index and the border of my chart. I highlighted the offending code in Blue.Any help is appreciated.

Tom

Function AddChartSheet(strSheet As String, intTopDataRow As Integer, intBottomDataRow As Integer, intLastColumn As Integer, intPageCnt As Integer)
    Dim cht As ChartObject
    Dim rng As Range
    Dim intFirstChartRow As Integer
    Dim intLastChartColumn As Integer
    Dim intLastChartRow As Integer
    Dim strLocation As String
    Dim strDataSource As String
    Dim strSource2 As String
    Dim chtChart As Chart
    Dim strNameSource As String
    'Set location of chart
    intFirstChartRow = intBottomDataRow + 2
    intLastChartRow = intFirstChartRow + 19
    intLastChartColumn = intLastColumn - 1
    strDataSource = ConvColLet(intLastColumn) & intTopDataRow & ":" & ConvColLet(intLastColumn) & intBottomDataRow
    strLocation = "A" & intFirstChartRow & ":" & ConvColLet(intLastColumn) & intLastChartRow
    strNameSource = "A" & intTopDataRow & ":" & "A" & intBottomDataRow
             
   Set chtChart = Charts.Add
   Set chtChart = chtChart.Location(WHERE:=xlLocationAsObject, Name:=strSheet)
   With chtChart
   'Set the chart type
    .ChartType = xlColumnClustered
    'Set the source data range for the chart.
    .SetSourceData Source:=Sheets(strSheet).Range(strDataSource), PlotBy:= _
        xlColumns
   End With
   With chtChart.Parent
      .Top = Range("A" & intFirstChartRow).Top
      .Width = Range(strLocation).Width
      .Height = Range(strLocation).Height
      .Left = Range("A1").Left
   End With
    'Remove Series Label
    ActiveChart.Legend.Select
    Selection.Delete
    'Add XAxisDataSeries
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).XValues = Worksheets(strSheet).Range(strNameSource)
    'Set up Font Structure for XValues
    ActiveChart.Axes(xlCategory).Select
    With Selection.TickLabels.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        If intPageCnt = 3 Then .Size = 9
    End With
    'ColorIndex definitions
     'Set up background color of chart
[Blue]    With Selection.Interior [/Blue]
    'Set Border color black and thin line
    .ColorIndex = 1
    .Background = xlAutomatic
    .Weight = xlThin
    .LineStyle = xlContinuous
    'Set background color to white on chart
    .ColorIndex = 2
    .PatternColorIndex = 1
    .Pattern = xlSolid
    End With
    'Reselect sheet so chart only is not printed
    goXL.ActiveSheet.Select ("A4")
End Function

 

[SkipVought]

Ya gotta crawl before you walk! Which is why i originally suggested doing iton the sheet and recording so you UNDERSTOOD what you were doing.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

The past two weeks is the first time I tried to use vba to program charts.

When did you start using Excel Charts?

How much do you know about Excel Charts?

You REALLY ought to have a GOOD working knowledge of Excel Charts before you start doing any fancy programming! THAT is all I am stating!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

I have been working with excel and access for over 10 years. Most of this experience has been with what I will call advanced user functions. Creating forms,queries and tables in access. Manually creating formulas, custom formatting and charts in excel.
I have done a little vba programming over the years so I am familiar with the basic concepts. My current job requires me to automate using data in access to create reports in excel. I have bought a couple of books that discuss the general concepts. What I have not found is a book that states if you want to do ..... this is the method and property that you would use. So I am left with trying my best to put some code together and asking for help with this board. To be honest with you with the help of this board I have made significant progress. So once again I thank you for all your assistance.

Tom