Color chart by cell, Conditional formatting

[brandon20]

Is there a way to color a bar chart by the color of a cell? I have found the VBA to do this, but it does not work for conditional formatting, I need it to work for conditional formatting..

Is this possible? I cant seem to find the answer.

Here is the code that does not work.


<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Sub CellColorsToChart()
Dim oChart As ChartObject
Dim MySeries As Series
Dim FormulaSplit As Variant
Dim SourceRange As Range
Dim SourceRangeColor 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

'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(1)
SourceRangeColor = SourceRange.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.MarkerBackgroundColor = SourceRangeColor
MySeries.MarkerForegroundColor = SourceRangeColor
MySeries.Format.Line.ForeColor.RGB = SourceRangeColor
MySeries.Format.Line.BackColor.RGB = SourceRangeColor
MySeries.Format.Fill.ForeColor.RGB = SourceRangeColor

Next MySeries
Next oChart

End Sub</code>

 

[brandon20]

I do not think you can use vba to color a chart by vba.

But since you can color a chart by cell color, is there a way to color a cell next to my conditionally formatted cell the same color as the conditional format?

lets say I have these values below, A is red because of its conditional format, is there a way to color the cell next to it red?
either through vba or through an equation in the cell?


[TABLE="width: 308"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]blue[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]green[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]yellow[/TD]
[/TR]
</tbody>[/TABLE]

Thanks