Charts background and font color depending to the color write inside the chart

[begins]

Hello to everybody,
I'm new in this spectacular forum!
I'm trying to create a VBA macro that change the background and font color of a chart, and the color is write inside the chart in RGB format, for example like this: RGB(255, 0, 0).

Is it possible?

Thank you very much

Wish you good Sunday

 

[Hui]

Begins

Firstly, Welcome to the Chandoo.org Forums

Yes, You can alter most aspects of charts by using macros in VBA

The best place to start is to setup a chart and the simply edit it whilst recording a macro

You will end up with some code like:

Sub Macro1()

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.FullSeriesCollection(1).Select
    With Selection.Format.Fill
      .Visible = msoTrue
      .ForeColor.RGB = RGB(255, 0, 0)
      .Transparency = 0
      .Solid
    End With
    ActiveChart.PlotArea.Select
    With Selection.Format.Fill
      .Visible = msoTrue
      .ForeColor.RGB = RGB(0, 176, 80)
      .Transparency = 0
      .Solid
    End With
    ActiveChart.ChartArea.Select
    With ActiveSheet.Shapes("Chart 1").Fill
      .Visible = msoTrue
      .ForeColor.RGB = RGB(0, 112, 192)
      .Transparency = 0
      .Solid
    End With
    ActiveChart.ChartTitle.Select
    With Selection.Format.TextFrame2.TextRange.Font.Fill
      .Visible = msoTrue
      .ForeColor.RGB = RGB(255, 0, 0)
      .Transparency = 0
      .Solid
    End With
End sub

You can read the code and it mostly identifies what it is doing

eg:
ActiveChart.PlotArea.Select
With Selection.Format.Fill

shows you are formatting the Plotarea

You can simplify this code such as:

Sub Macro2()

    With ActiveSheet
.ChartObjects("Chart 1").Activate
    .FullSeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(255, 255, 0)
    .PlotArea.Format.Fill.ForeColor.RGB = RGB(0, 176, 100)
    .Shapes("Chart 1").Fill.ForeColor.RGB = RGB(0, 112, 102)
    .ChartTitle.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(255, 0, 255)
End with
End Sub

The biggest hangup with this is that the full object model is not available to you in VBA, that is you will find every now and then actions that cannot be completed in VBA because the Object Model hasn't been fully exposed to VBA

In these cases you have limited opportunities to get around the uissues

Some times you will also get code that after being recorded will not actually work

Google is your friend here, simply search for how others have got around these issues

Of course you are always welcome to come back here for assistance when you get stuck