Conditional Formatting of Excel Charts

Steven Reid
Steven Reid used Ask the Experts™
on
Hi,

I am trying to produce a number of excel charts that have different views of some data.

The data is in the following format.
Type	SubType	Value
A	1	100
A	2	150
A	3	125
B	1	100
B	2	175
B	3	150
B	4	125
C	1	100
C	2	200
C	3	150

Open in new window


What we would like to do is use consistent colors in the charts, where we give Type A  a color of Green, and vary the SubType by shades of green.
Give Type B a color of Blue, and vary the SubType by shades of blue.
And so on.
Coloured-Chart.png

Is this possible by conditional formatting or by VBA code?

Thanks
Steve Reid
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Steven ReidHead of IT

Author

Commented:
Hi Sachiek,

That works for cells, but doesn't do anything for a chart.
I have come across this macro which works for multiple series, but am going to try to convert it to the one series.

Thanks
Top Expert 2016
Commented:
Hi,

pls try

 Sub macro()
 ArrColors = Array(RGB(255, 0, 0), RGB(0, 128, 0), RGB(0, 0, 255))
 Set FirstSerie = ActiveChart.SeriesCollection.Item(1)
 arrXValues = FirstSerie.XValues
 Set pts = FirstSerie.Points
 LastEl = ""
 IdxCol = 0
 For Idx = 1 To UBound(arrXValues)
    TranspRatio = 1 / (UBound(Filter(arrXValues, arrXValues(Idx))) + 1)
    If arrXValues(Idx) <> LastEl Then
        col = ArrColors(IdxCol)
        With pts.Item(Idx).Format.Fill
            .Visible = msoTrue
            .ForeColor.RGB = col
            .Transparency = 0
            dblTransparency = .Transparency
            .Solid
        End With
        dblTransparency = 0
        IdxCol = IdxCol + 1
    Else
        With pts.Item(Idx).Format.Fill
            .Visible = msoTrue
            .ForeColor.RGB = col
            .Transparency = dblTransparency + TranspRatio
            dblTransparency = .Transparency
            .Solid
        End With
        
    End If
    LastEl = arrXValues(Idx)
 Next
 
 End Sub

Open in new window

Regards
EE20151125.xlsm
Steven ReidHead of IT

Author

Commented:
Thanks Rgonzo1971.
that was a good pointer to what i needed

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial