tommy bak

08-23-2004, 08:28 AM

HI

I've been playing a little with the possibility to format a chart conditionally and I have got it working now.

My problem is that I would like it to be more flexible, but right now I seem to be a bit braindead and need some help :help

It should be more flexible on the numbers of conditions and corresponding colors.

Right now it's locked to 3 (bad, good, exelent)

I have made it as a classmodule, becaurse I want to include it in a couple of workbooks. As it is my first classmodule, please give some feedback.

[Edit] : Complete file is attached

Class module:

Option Explicit

'repaints charts within 2 limits in 3 different colors

Public WithEvents Graph As Chart

'Limits for coloring

Dim L2 As Long, L3 As Long

'Colors for each section

Dim C1 As Long, C2 As Long, C3 As Long

'holder for series-number

Dim S As Long

Private Sub Graph_Calculate()

Dim y As Long

Dim c As Range

Dim rngChart As Range

Dim vaGetRange

'Split the chartformula at ,

vaGetRange = Split(Graph.SeriesCollection(S).Formula, ",")

'choose the text between 2 and 3 comma;

Set rngChart = Range(vaGetRange(2))

y = 0

'for each cell in the area rngChart find the value and color the chart's

'point with the corresponding color

For Each c In rngChart

y = y + 1

With Graph.SeriesCollection(S).Points(y).Interior

'this is where I would like to be able to insert multible conditions

Select Case c.Value

Case Is < L2: .ColorIndex = C1

Case L2 To L3: .ColorIndex = C2

Case Is > L3: .ColorIndex = C3

Case Else: Exit Sub

End Select

End With

Next

End Sub

Sub Limits(Low, Hi)

L2 = Low

L3 = Hi

End Sub

Sub Colors(Color_Low, Color_Med, Color_Hi)

C1 = Color_Low

C2 = Color_Med

C3 = Color_Hi

End Sub

Property Let Serie(Number As Long)

S = Number

End Property

Property Get Serie() As Long

Serie = S

End Property

Standard module:

Option Explicit

'create new GraphClass object

Dim clsGr1 As New clsGraph '1. chart

Dim clsGr2 As New clsGraph '2. chart

Sub InitializeChart()

'choose the chart to be colored

'call this sub from Workbook_Open or manually

With clsGr1

Set .Graph = shUnd.ChartObjects(1).Chart

.Serie = 1

.Limits 3, 4

.Colors 3, 6, 4

End With

With clsGr2

Set .Graph = shUnd.ChartObjects(2).Chart

.Serie = 1

.Limits 3, 4

.Colors 3, 6, 4

End With

End Sub

TIA

Tommy Bak

I've been playing a little with the possibility to format a chart conditionally and I have got it working now.

My problem is that I would like it to be more flexible, but right now I seem to be a bit braindead and need some help :help

It should be more flexible on the numbers of conditions and corresponding colors.

Right now it's locked to 3 (bad, good, exelent)

I have made it as a classmodule, becaurse I want to include it in a couple of workbooks. As it is my first classmodule, please give some feedback.

[Edit] : Complete file is attached

Class module:

Option Explicit

'repaints charts within 2 limits in 3 different colors

Public WithEvents Graph As Chart

'Limits for coloring

Dim L2 As Long, L3 As Long

'Colors for each section

Dim C1 As Long, C2 As Long, C3 As Long

'holder for series-number

Dim S As Long

Private Sub Graph_Calculate()

Dim y As Long

Dim c As Range

Dim rngChart As Range

Dim vaGetRange

'Split the chartformula at ,

vaGetRange = Split(Graph.SeriesCollection(S).Formula, ",")

'choose the text between 2 and 3 comma;

Set rngChart = Range(vaGetRange(2))

y = 0

'for each cell in the area rngChart find the value and color the chart's

'point with the corresponding color

For Each c In rngChart

y = y + 1

With Graph.SeriesCollection(S).Points(y).Interior

'this is where I would like to be able to insert multible conditions

Select Case c.Value

Case Is < L2: .ColorIndex = C1

Case L2 To L3: .ColorIndex = C2

Case Is > L3: .ColorIndex = C3

Case Else: Exit Sub

End Select

End With

Next

End Sub

Sub Limits(Low, Hi)

L2 = Low

L3 = Hi

End Sub

Sub Colors(Color_Low, Color_Med, Color_Hi)

C1 = Color_Low

C2 = Color_Med

C3 = Color_Hi

End Sub

Property Let Serie(Number As Long)

S = Number

End Property

Property Get Serie() As Long

Serie = S

End Property

Standard module:

Option Explicit

'create new GraphClass object

Dim clsGr1 As New clsGraph '1. chart

Dim clsGr2 As New clsGraph '2. chart

Sub InitializeChart()

'choose the chart to be colored

'call this sub from Workbook_Open or manually

With clsGr1

Set .Graph = shUnd.ChartObjects(1).Chart

.Serie = 1

.Limits 3, 4

.Colors 3, 6, 4

End With

With clsGr2

Set .Graph = shUnd.ChartObjects(2).Chart

.Serie = 1

.Limits 3, 4

.Colors 3, 6, 4

End With

End Sub

TIA

Tommy Bak