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