Clustered Bar Chart

[excelprogrammer]

Hi,
I am trying to create this chart with dates on x axis and clustered bar chart on y axis showing availability of equipment(Rented,Quoted,Available).

I tried to write the following macro for the above requirement but i cant get x axis to show the dates and the bar chart on y axis does not show different colours for different status of equipment.

The data is as follows

A1:28882 C1:Status
A2:09/09/2005 C2:Rented
A3:09/16/2005 C3:Quoted

The macro is as follows -----------------

Sub MakeRental()
 
Dim i As Integer
 
Worksheets("Rental").Select
Worksheets("Rental").Range("A2:A3").Select
'Selection.DateFormat = "mm/dd/yyyy"
            
            Charts.Add
        ActiveChart.Location Where:=xlLocationAsObject, Name:="Rental"
            ActiveChart.ChartType = xlBarClustered
 ActiveChart.SetSourceData Source:=Sheets("Rental").Range("R10"), PlotBy:=xlRows
          ActiveChart.SetSourceData Source:=Sheets("Rental").Range("A1:A3"), PlotBy:=xlRows
        
    With ActiveChart
        .HasLegend = True
        .Legend.Select
        Selection.Position = xlRight
        .SeriesCollection(1).Name = "=""Rented"""
        With ActiveChart.SeriesCollection.NewSeries
            .Name = "Quoted"
            .XValues = ActiveSheet.Range("A2:A3")
        End With
        With ActiveChart.SeriesCollection.NewSeries
            .Name = "Available"
        End With
        .HasDataTable = False
        .HasTitle = True
        .ChartTitle.Characters.Text = "Rental Availability Chart"
    End With
 
        ActiveChart.SeriesCollection(1).Select
        With ActiveChart.ChartGroups(1)
            .Overlap = 100
            .GapWidth = 150
            .HasSeriesLines = False
        End With
     
     For i = 1 To 2
            ActiveChart.SeriesCollection(1).Select
            With Selection.Border
                .Weight = xlThin
                .LineStyle = xlAutomatic
            End With
            Selection.Shadow = False
            Selection.InvertIfNegative = False
            With Selection.Interior
            If Worksheets("Rental").Cells(i+1 , 3) = "Rented" Then
                .ColorIndex = 4 'green
            Else
                If Worksheets("Rental").Cells(i+1 , 3) = "Quoted" Then
                    .ColorIndex = 3 'red
                End If
            End If
            .Pattern = xlSolid
            End With
            ActiveChart.ChartGroups(1).SeriesCollection(1).PlotOrder = 1
    Next i
 
        With ActiveSheet.ChartObjects(1).Chart.Axes(xlCategory)
           ' .MinimumScale = 9 / 9 / 2005
            '.MaximumScale = 9 / 25 / 2005
        End With
End Sub

---------------------------------------------------

Regards,

[DKenny]

So what is the problem?

[RobDog888]

Welcome to the Forums.

...i cant get x axis to show the dates and the bar chart on y axis does not show different colours for different status of equipment.

..

[DKenny]

For each series; Rented,Quoted,Available - how are you determining the start and end dates?

[excelprogrammer]

Let me explain the data in the fields

The data is as follows

A1:28882 C1:Status
A2:09/09/2005 C2:Rented
A3:09/16/2005 C3:Quoted

This means that 28882,which is an equipment unit , has status Rented from date 9 Sep to 15 Sep and has status Quoted from 16 Sep onwards till end of month.

The max and min values for x axis is given by the following code, which I came to know of this after posting the first thread in vbforums.com.

The chart is meant for plotting status of equipment in different colours (on a bar on y axis)with respect to dates on x axis.

I also would like to know how can i set .Majorunit in the following code.

MinVal = #9/9/2005#
MaxVal = #9/25/2005#
With ActiveSheet.ChartObjects(1).Chart.Axes(xlValue)
.MinimumScale = MinVal
.MaximumScale = MaxVal
End With

When i write the above code, the bar on the y axis does not appear anymore.I need to draw a bar on y axis which will show different colours for different status.

Thanks and Regards