Using data from coloured cells in a chart

[Mikey9131]

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

 

[Gord Dibben]

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