Chart conditional formatting, VBA

[WillamYan]

Hello))

I am trying to conditionally format my chart. I know that I can do it with "IF" statement (which I do successfully). But I want to format the fill color of my chart bars based on the cells' color (bars take the same color as cells). Something tells me that there should be an easy VBA code to do this. I am using the following code, but it does not work.


Sub ColorChartColumnsbyCellColor()
With Sheets("Data").ChartObjects(1).Chart.SeriesCollection(1)
Set vAddress = ActiveSheet.Range(Split(Split(.Formula, ",")(1), "!")(1))
For i = 1 To vAddress.Cells.Count
.Points(i).Format.Fill.ForeColor.RGB = ThisWorkbook.Colors(vAddress.Cells(i).Interior.ColorIndex)
Next i
End With
End Sub

Country	2015
Jamaica	15
Mexico	100
USA	18
Canada	250
Chile	33

<tbody>
</tbody>

 

[Sektor]

As far as I understand you did not use DisplayFormat here, right?

Did you see the code to make that statement?

To be honest I can't find this DisplayFormat property in excel?

Starting with Excel 2010, a new property DisplayFormat which made this task super easy.

It means that it doesn't exist in <=Excel 2007.

 

[WillamYan]

Yes I saw.
I am not an experienced user of VBA, so when I ask something it means I did not understand. I always check what more experienced one tells me, before asking the next question.

Thanks anyway, your code is working. The only question I had was what is DisplayFormat, how should I access it. (I am using excel 2013). I am hearing this term first time.

 

[Sektor]

According to help, DisplayFormat object represents the display settings for the specified range.
It means that it represents even what conditional formatting had set.
DisplayFormat exists from Excel 2010 and on. So, in Excel 2013 there will be no problem with it

 

[WillamYan]

Thanks!

Everything is clear. One last question, if you don't mind. When I link the data to another sheet ("Data"B2="Sheet1"B2), it does not function. I need to run the macro every time there is a change in the source data (in "Sheet1"B2). How can I work around this issue?

 

[Jon Peltier]

Hi Sektor -

Not to be a pedant, and I'm sure your code is very nicely done. But it would be nice if you posted it in your answers, so we don't have to download a workbook to read it.