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
|