Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Ferrero's Conditional Bar Chart Qs
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 he http://www.edferrero.com/ExcelCharts...2/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 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Ferrero's Conditional Bar Chart Qs
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. -- John Mansfield http://cellmatrix.net "Ray" wrote: 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 he http://www.edferrero.com/ExcelCharts...2/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 |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Ferrero's Conditional Bar Chart Qs
Thanks John! A tweak here and there ... and it works perfectly!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Chart | Excel Discussion (Misc queries) | |||
Conditional chart series. | Charts and Charting in Excel | |||
Conditional chart | Charts and Charting in Excel | |||
Conditional formatting a bar chart | Charts and Charting in Excel | |||
Conditional formatting in a chart | Charts and Charting in Excel |