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



[TABLE="width: 500"]
<tbody>[TR]
[TD]Country[/TD]
[TD]2015[/TD]
[/TR]
[TR]
[TD]Jamaica[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Mexico[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]Canada[/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD]Chile[/TD]
[TD]33[/TD]
[/TR]
</tbody>[/TABLE]

 

[Sektor]

.Points(i).Format.Fill.ForeColor.RGB = vAddress(i).Interior.Color

But in order for it to work, you need to do one of the following:
1) Right-click on series and go to: Format Data Series -> Fill -> check "Vary colors by point"
2) Set in code this line of code: Sheets("Data").ChartObjects(1).Chart.ChartGroups(1).VaryByCategories = True

 

[WillamYan]

Thanks but unfortunately, not working. I found the following code. This one works fine, BUT when I change the style (not type) of the chart, the bars disappear.
Any ideas why?

Sub CellColorsToChart()
Dim oChart As ChartObject
Dim MySeries As Series
Dim FormulaSplit As Variant
Dim SourceRange As Range
Dim SourceRangeColor As Long
Dim NumberofDataPoints As Long
Dim iPoint As Long

'Loop through all charts in the active sheet
For Each oChart In ActiveSheet.ChartObjects

'Loop through all series in the target chart
For Each MySeries In oChart.Chart.SeriesCollection

NumberofDataPoints = MySeries.Points.Count

For iPoint = 1 To NumberofDataPoints

'Get Source Data Range for the target series
FormulaSplit = Split(MySeries.Formula, ",")

'Capture the first cell in the source range then trap the color
Set SourceRange = Range(FormulaSplit(2)).Item(iPoint)
SourceRangeColor = SourceRange.DisplayFormat.Interior.Color

On Error Resume Next
'Coloring for Excel 2003
'MySeries.Interior.Color = SourceRangeColor
' MySeries.Border.Color = SourceRangeColor
' MySeries.MarkerBackgroundColorIndex = SourceRangeColor
' MySeries.MarkerForegroundColorIndex = SourceRangeColor

'Coloring for Excel 2007 and 2010
MySeries.Points(iPoint).MarkerBackgroundColor = SourceRangeColor
MySeries.Points(iPoint).MarkerForegroundColor = SourceRangeColor
MySeries.Points(iPoint).Format.Line.ForeColor.RGB = SourceRangeColor
MySeries.Points(iPoint).Format.Line.BackColor.RGB = SourceRangeColor
MySeries.Points(iPoint).Format.Fill.ForeColor.RGB = SourceRangeColor

Next
Next MySeries
Next oChart

End Sub

 

[Sektor]

You asked one question but you really had another question. I have answered your question - and this code works. Here's example workbook.

 

[WillamYan]

Thanks a lot Sektor.
Everything works perfectly now. Just one more question. Is is possible to automate this process? I mean, when a cell in the data range changes, the code runs automatically.

 

[Sektor]

Beg your pardon, but how changing cell's value affects bar's colors? I mean, your original question was about applying color to the bar which the point (cell) has.

 

[WillamYan]

Beg your pardon, but how changing cell's value affects bar's colors? I mean, your original question was about applying color to the bar which the point (cell) has.

I use conditional formatting for my range. And insert a bar chart. Once the cell value goes up , the cell changes its color, hence the respective bar changes its color as well.

 

[WillamYan]

Any ideas how to make this code run automatic once a cell entry changes?

 

[Sektor]

I use conditional formatting for my range.

Why you didn't tell it in the first place? This is important!
The thing is that it's very hard to get cell color from the cell which has conditional formatting if you have <=Excel 2007 (I personally didn't try it).
Starting with Excel 2010, a new property DisplayFormat which made this task super easy.
So, here's example workbook. I have applied some conditional formatting. Also I added Worksheet_Change event handler to react to cells' changes. Try to change values from 15 to 29 to see the effect.

 

[WillamYan]

Why you didn't tell it in the first place? This is important!
The thing is that it's very hard to get cell color from the cell which has conditional formatting if you have <=Excel 2007 (I personally didn't try it).
Starting with Excel 2010, a new property DisplayFormat which made this task super easy.
So, here's example workbook. I have applied some conditional formatting. Also I added Worksheet_Change event handler to react to cells' changes. Try to change values from 15 to 29 to see the effect.

Thanks.

As far as I understand you did not use DisplayFormat here, right? To be honest I can't find this DisplayFormat property in excel? maybe I am a bit lost