Hi ,
I have tried the following , and it may be what you are looking for. Try it and let me know.
Public Sub Chart_Bars_Color_Change()
Dim range_colors() As Long
Application.ScreenUpdating = False
ThisWorkbook.Worksheets("Sheet1").ChartObjects(1).Select
With ActiveChart.SeriesCollection(1)
Number_of_values = .Points.Count
ReDim range_colors(Number_of_values) As Long
chart_series = .Formula
series_range = get_range(chart_series)
For i = 1 To Range(series_range).FormatConditions.Count
color_entered = False
current_color = Range(series_range).FormatConditions.Item(i).Interior.Color
For j = 1 To Number_of_values
If ((range_colors(j) = 0) And (Not color_entered)) Then
range_colors(j) = current_color
color_entered = True
Else
If range_colors(j) = current_color Then color_entered = True
End If
Next
Next
For i = 1 To Number_of_values
.Points(i).Interior.Color = range_colors(i)
Next
End With
Application.ScreenUpdating = True
End Sub
Public Function get_range(ByVal text_string As String)
len_string = Len(text_string)
If len_string < 1 Then
get_range = Null
Else
comma_count = 0
i = 1
Do
ch = Mid(text_string, i, 1)
If ch = "," Then comma_count = comma_count + 1
i = i + 1
Loop Until comma_count >= 2
get_range = ""
Do
ch = Mid(text_string, i, 1)
If ch = "," Then
comma_count = comma_count + 1
Else
get_range = get_range & ch
End If
i = i + 1
Loop Until comma_count >= 3
End If
End Function
Notes :
1. I have not been able to make it work with gradient color scales.
2. It works with colors which are a result of formulae. But even in this , the cell interior colors are all white ; the cell color which is a result of the conditional format is solely in the CF formula !
3. Suppose the working range is , say D6:D9 ; the conditional formatting rules for these cells should be in the correct order i.e. the rule which gives cell D6 its color should be first in the list ; the rule which gives cell D7 its color should be second in the list , and so on. This is because , within the procedure , the colors are retrieved in the order in which they have been entered. If the order is wrong , then the chart bar colors may not correspond with the range cell colors !
Hopefully , others can improve on this.
Narayan
Subscribe
