Conditional Formatting of Excel Charts


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.

Is this possible by conditional formatting or by VBA code?

Steve Reid
Steven ReidHead of ITAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steven ReidHead of ITAuthor 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.


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
        End With
        dblTransparency = 0
        IdxCol = IdxCol + 1
        With pts.Item(Idx).Format.Fill
            .Visible = msoTrue
            .ForeColor.RGB = col
            .Transparency = dblTransparency + TranspRatio
            dblTransparency = .Transparency
        End With
    End If
    LastEl = arrXValues(Idx)
 End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steven ReidHead of ITAuthor Commented:
Thanks Rgonzo1971.
that was a good pointer to what i needed
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.