I have a worksheet with various Rows and in some cases individual Cells
colour coded. Is it posible to set up say a Sub or Function to Filter out
either Rows or Cells based on the colour they contain.

Any help would be appreciated.

Regards.

Fred

Fred,

Not possible to directly filter by colour, but you could setup a UDF in VBA
and a helper column to get the ColorIndex and filter on that.

For instance, the UDF would look like

Function ColorIndex(rng As Range)
If rng.Count 1 Then
ColorIndex = CVErr(xlValue)
Else
ColorIndex = rng.Interior.ColorIndex
End If
End Function

and your helper column would have the formula
=colorindex(A1)
as an example.

Then filter the helper column by colour number (lookup the ColoIndex
Property in the VBA help, you will see the 56 colours in the standard
palette listed there).


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Frederick" wrote in message
...
I have a worksheet with various Rows and in some cases individual Cells
colour coded. Is it posible to set up say a Sub or Function to Filter out
either Rows or Cells based on the colour they contain.

Any help would be appreciated.

Regards.

Fred

Hi Frederick,
Yes it is possible but not recommended.
It would be better to use the criteria that you use to color
the cells rather than trying to base the count on the color.

You can color a cell directly or you can color a cell by
Conditional Formatting, and fonts can be additionally
changed by number formatting (C.F. overrides other formatting).

To make any count change instantly when you change a color
is going to at least require the use of Volatile in your user
defined funtion (UDF) -- not very efficient, but something
you may not notice on a small workbook.

You can use the UDF on Chip Pearson's site (not for conditional formatting)
http://www.cpearson.com/excel/colors.htm

Some examples in
http://google.com/groups?threadm=u9b...%40cpmsnbbsa02

Color Palette and the 56 Excel ColorIndex Colors
http://www.mvps.org/dmcritchie/excel/colors.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Frederick" wrote in message ...
I have a worksheet with various Rows and in some cases individual Cells
colour coded. Is it posible to set up say a Sub or Function to Filter out
either Rows or Cells based on the colour they contain.

Any help would be appreciated.

Regards.

Fred

Thank you both for helping me out here, your informatin is of great
assistance.

Best Regards Fred

"Frederick" wrote in message
...
I have a worksheet with various Rows and in some cases individual Cells
colour coded. Is it posible to set up say a Sub or Function to Filter out
either Rows or Cells based on the colour they contain.

Any help would be appreciated.

Regards.

Fred

Hi Fred,
Thanks, feedback is appreciated, but it would be best not to change
the subject title.

The original subject title was pretty well done, it describes
the problem, something that seems to forgotten a lot.
(spell checker doesn't work on subjects in OE).
---
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

"Frederick" wrote in ...
Thank you both for helping me out here, your information is of great
assistance.