Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using data from coloured cells in a chart
I'd like to be able to use data that is entered onto worksheet to produce a
number of charts, but only want each chart to use data from cells that are formatted in a certain fill colour. On other words, one chart might use data from cells coloured red, another chart from cells coloured green etc. is this possible and how would I go about doing it? Any help gratefully appreciated |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using data from coloured cells in a chart
Mikey
Assuming the cells are colored manually and not by Conditional Formatting you could first find the color index numbers of the cells then AutoFilter on those numbers. Say you have data in A1:A200. Creat a chart based on that range. In Column B enter the formula =CellColorIndex(cellref) Double-click on the fill handle to copy down to end of data in Column A. The CellColorIndex Function is a User Defined Function from Chip Pearson. Function CellColorIndex(inRange As Range, Optional _ OfText As Boolean = False) As Integer ' This function returns the ColorIndex value of a the Interior ' (background) of a cell, or, if OfText is true, of the Font in the cell. Application.Volatile True If OfText = True Then CellColorIndex = inRange.Font.ColorIndex Else CellColorIndex = inRange.Interior.ColorIndex End If End Function The UDF would be copied and placed into a General Module in your workbook. Filter on the numbers you get in the column. Your chart will change to reflect the filtered data. If you want a chart for each set of filtered data, create one for each. Gord Dibben Excel MVP On Sat, 26 Nov 2005 11:17:06 -0800, Mikey9131 wrote: I'd like to be able to use data that is entered onto worksheet to produce a number of charts, but only want each chart to use data from cells that are formatted in a certain fill colour. On other words, one chart might use data from cells coloured red, another chart from cells coloured green etc. is this possible and how would I go about doing it? Any help gratefully appreciated |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combination chart with three data ranges | Charts and Charting in Excel | |||
Fan charts | Charts and Charting in Excel | |||
Help making a chart that doesn't graph cells without data? | Excel Discussion (Misc queries) | |||
Help with data not getting plotted | Excel Discussion (Misc queries) | |||
Extending a Chart Data Series from an Array - Can it be done? | Charts and Charting in Excel |