View Single Post
  #10   Report Post  
Posted to microsoft.public.excel.programming
Rick Rothstein \(MVP - VB\) Rick Rothstein \(MVP - VB\) is offline
external usenet poster
 
Posts: 2,202
Default Filtering by Cell Background Color

The data I wish to filter exists in a single column (e4:e900) of two
differing colors. I have used the user defined function:

Function tellmecolor(colorcell As Range) As Integer
tellmecolor = colorcell.Interior.ColorIndex
End Function

to define the values of my interior cell colors thus:
red = 3
green = 10

Now I can't figure out how to use the user defined function to place an
integer in each cell within the (e4:e900) range so I can filter out the
10's.


Dave gave you a Macro-As-Formula solution, but since you posted in the
excel.programming newsgroup, the possibility exists that you were looking
for a single function call to do all the work (for example, in response to
the click of a CommandButton). This modification to your function will do
that...

Function TellMeColor(ColorCells As Range) As Integer
Dim R As Range
For Each R In ColorCells
R.Value = R.Interior.ColorIndex
Next
End Function

If you go with the CommandButton activation method, here is what its Click
event would look like...

Private Sub CommandButton1_Click()
TellMeColor Range("E4:E900")
End Sub

for the fixed range you specified. If you wanted the function to operate on
a user defined selection, the Click event would look like this...

Private Sub CommandButton1_Click()
TellMeColor Selection
End Sub

Just highlight the cells of interest and click the button.

Rick