Conditionally fill data series with another color based on month

Dear Experts:

A chart with figures for the last 12 months is depicted in the active worksheet
Every month the figure for the 'oldest' month is deleted and the figure for the latest month is added

The fill for the 2015 data series should be red, i.e. whenever a new month of the current year is added
the fill for that data point is to be filled red automatically, using a 'Worksheet_Change(ByVal)' macro

That is, every month a new red data point appears and a blue one is dropped.

Chart-Continuous-12-month-1.png
Chart-Continuous-12-month-2.png
I have attached a sample file for your convenience.

Help is much appreciated. Thank you very much in advance.  

Regards, Andreas

Data-Series-Color-Depending-on-month.xls
Andreas HermleTeam leaderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Rgonzo1971Commented:
HI,

Are you sure to want a macro and not formulas see example

Regards
EE20150410.xlsm
Rgonzo1971Commented:
HI,

if the data comes automatically you could use OnOpen event instead of onChange

Sub Macro1()
'
' Macro1 Macro
'
    Dim DataArray() As Variant
'
    Set g = Sheets("Conditonal_Formatting").ChartObjects(1).Chart
    For Each col In g.SeriesCollection
        DataArray = col.XValues
        For Idx = 1 To col.Points.Count
            If Year(DataArray(Idx)) = Year(WorksheetFunction.Min(DataArray)) Then
                col.Points(Idx).Format.Fill.ForeColor.RGB = RGB(142, 180, 227)
            Else
                col.Points(Idx).Format.Fill.ForeColor.RGB = RGB(230, 185, 184)
            End If
        Next
    Next
    
End Sub

Open in new window

Regards
Andreas HermleTeam leaderAuthor Commented:
Hi Rgonzo,

thank you very much for your swift and professional support. Works great, thank you very much for it,  but as a matter of fact I favor a macro solution on this question, i.e. I guess it will be a  'Worksheet_Change(ByVal)' macro solution, won't it?

Thank you very much in advance. Regards, Andreas
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Rgonzo1971Commented:
then try

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim DataArray() As Variant
'
    Set g = Sheets(Target.Parent.Name).ChartObjects(1).Chart
    For Each col In g.SeriesCollection
        DataArray = col.XValues
        For Idx = 1 To col.Points.Count
            If Year(DataArray(Idx)) = Year(WorksheetFunction.Min(DataArray)) Then
                Set pt = col.Points(Idx)
                col.Points(Idx).Format.Fill.ForeColor.RGB = RGB(142, 180, 227)
            Else
                col.Points(Idx).Format.Fill.ForeColor.RGB = RGB(230, 185, 184)
            End If
        Next
    Next
    
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
Saurabh Singh TeotiaCommented:
No Points for this small changes in Rgonzo code to do what you are looking for.. I have assumed that the last series will be the one which you want to highlight red as that will be previous year one for sure...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim DataArray() As Variant
    '
    Set g = Sheets(Target.Parent.Name).ChartObjects(1).Chart
    For Each col In g.SeriesCollection
        DataArray = col.XValues
        For Idx = 1 To col.Points.Count
            If Year(DataArray(Idx)) = Year(WorksheetFunction.Min(DataArray)) Then
                Set pt = col.Points(Idx)
                col.Points(Idx).Format.Fill.ForeColor.RGB = RGB(142, 180, 227)
            Else
                If Idx = col.Points.Count Then
                    col.Points(Idx).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
                Else
                    col.Points(Idx).Format.Fill.ForeColor.RGB = RGB(230, 185, 184)
                End If
            End If
        Next
    Next

End Sub

Open in new window


Saurabh...
Andreas HermleTeam leaderAuthor Commented:
Dear both,

thank you so much for your help. Will do some testing tonite and then let you know. Regards, Andreas
Andreas HermleTeam leaderAuthor Commented:
Just checked it, I am again very, very impressed by your expertise. Thank you very much for your great and professional support.

As a matter of fact, I could use both codes.

Regards, Andreas
Andreas HermleTeam leaderAuthor Commented:
Rgonzo,  if you do not mind, although Saurabh does  not want any points for his contribution, I would like to split the points, 400:100, would that be ok for you?

Regards, Andreas
Saurabh Singh TeotiaCommented:
Hi Andreas,

I didn't posted for points..feel free to allocate all the points to Rgonzo as i just tweaked his code only a bit basis of your need..

Saurabh...
Andreas HermleTeam leaderAuthor Commented:
Okay, thank you saurabh, very kind, I will do what you want. Again, thank you very much for your great and professional support. Regards, Andreas
Andreas HermleTeam leaderAuthor Commented:
Great coding, thank you very much for it
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.