# Conditional Formatting of Excel Charts

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
``````

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?

Thanks
Steve Reid
###### 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.

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
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
``````
Regards
EE20151125.xlsm

Experts Exchange Solution brought to you by