Ferrero's Conditional Bar Chart Qs

[Ray]

hello -

I've discovered Ed Ferrero's Conditional Bar Chart code and downloaded
the sample workbook, as I think it could be very useful in my work.
You can find it here:
http://www.edferrero.com/ExcelCharts/tabid/102/Default.aspx

However, I need to make some modifications and am having a very hard
time following the coding .... so, I can't make the necessary
modifications! In theory, it should be fairly easy ... my situation
is this:
** User makes a selection in cell A7 (via Validation)
** this value if fed (via direct link) to A97 ... B97:AN97 contain an
HLOOKUP based on A97
** B97:AN97 are conditionally formatted (interior.colorindex=red) if
cell value is > a permanent number
** A97:AN97 is used as the source_range for my bar chart

So, all I want to do is make the chart bars the same color as the cell
color in range B97:AN97...

Can anyone help to make these changes?

thanks, ray

 

[John Mansfield]

You could use a Worksheet_Change event with the following code and probably
achieve the same goal (I'm guessing).

To do so, copy this code into the sheet module in which you chart resides
(I'm assuming an embedded chart).

Name the chart something that you can specify in the code below. In this
example I've named the chart "Cht1".

The procedure will fire any time the worksheet is changed.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cht As ChartObject
Dim Color As Integer

Set Cht = ActiveSheet.ChartObjects("Cht1")

Cnt = 1

'This is the range that includes the colored cells
For Each Rng In Range("$B$97:$AN$97")
Color = Rng.Interior.ColorIndex
Set Pts = Cht.Chart.SeriesCollection(1).Points(Cnt)
Pts.Interior.ColorIndex = Color
Cnt = Cnt + 1
Next Rng

End Sub

Hope this might help.

 

[Ray]

Thanks John! A tweak here and there ... and it works perfectly!