• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Set Chart Series Colors to Match Source Cell Colors Based on Conditional Format

N

NJ786

New Member
Hi There


I Have a series of data either horizontally or vertically, and there are values ranging from 0 - whatever value. the cells are conditionally formatted to show a gradient based on high or low values in the series.... now can we have a bar chart which can Automatically Set Chart Series Colors to Match Source Cell Colors...


I found this nice post on the web, but it does not serve my requirement as it only takes the first color of the range and displays the bar as that color in the corresponding cell (one color not a gradient)


http://datapigtechnologies.com/blog/index.php/automatically-set-chart-series-colors-to-match-source-cell-colors/comment-page-1/#comment-21999


Can any one shed some light on this...


Appreciated.
 
Hi ,


Sorry , but I could not get any idea on this. I think if you conditionally format based on formulae , it will be possible , but if you just use a colour scale ( gradient ) , it may not.


Narayan
 
you have already gone through the example in the other post on the web, now what i was trying to get as you mentioned that once the values (eg: percentage) should change on a particular cell then it would show a different gradient based on the conditional format chosen. We can make the bars on a chart become a certain color but different colors based on the corresponding cell in the conditional format column is a bit tricky, could you please provide the formula for the conditional format, you can take any data for an example range of values based on percentage or a scale of 0-100....


I have been trying to get these working but not able to get them right, as these charts go for presentation and we sometimes need to change a few values in the data table to get the results in the chart....


Any help will be appreciated.


NJ
 
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
 
You must log in or register to reply here.
Back
Top