Conditional formatting for a graph 1

[LloydDobler]

Is there a way I can make bars on graphs appear in a different colour based on the value? I know I can do conditional formatting on the cell, but I want to go one step further and apply it to the graph as well.

"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."

 

[mintjulep]

Not directly.

As a work-around, you can create different data series based on value criteria, then plot all of the series.

 

[WinblowsME]

Use the macro recorder.

Data Sheet Name  :  Data
Chart Sheet Name :  Chart
Kind of Chart    :  Column Chart

Format of Data Sheet
----------------------------------------
Col_1   |   Col_2    |   Col_3
----------------------------------------
Row 1       Times        AvgCallsOffered
Row 2       3:00PM       16
Row 3       3:15PM       16.5
Row 4       3:30PM       16.33
Row 5       3:45PM       18.17
Row 6       4:00PM       12.67
Row 7       4:15PM       13
Row 8       4:30PM       9.5
Row 9       4:45PM       6.83
Row 10      5:00PM       0

' Put the following code in "Chart"

Private Sub Chart_Activate()
   Dim i As Integer, curr_data As Integer
   
   For i = 2 To 10
      curr_data = CInt(Sheets("Data").Cells(i, 3))

      With ActiveChart.SeriesCollection(1).Points(i - 1).Interior
         If curr_data >= 5 And curr_data <= 10 Then
            .ColorIndex = 10
         ElseIf curr_data >= 11 And curr_data <= 15 Then
            .ColorIndex = 20
         Else
            .ColorIndex = 30
         End If
         
         .Pattern = xlSolid
      End With
   Next i
End Sub

 

[onedtent]

if you give it little bit of thought, mintjulep's suggestion can work wonders.

- onedtent Onedtent OnedTent

 

[SkipVought]

Ditto on mintjulip's suggestion.

It's the ONLY way to go using native speadsheet functionality.

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[LloydDobler]

Data is being imported from SQL to XL, so I guess I'll convince them to use SQL reporting services instead. Thanks everyone for advice and quick responses.

"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."

 

[onedtent]

mint... a star 4 u

- onedtent Onedtent OnedTent