Filtering by Cell Background Color

L

LoboNetwork

Hello,

I have a large populated spreadsheet. Certain random cells of this
spreadsheet have a background color of red. Is there anyway to go down each
row of the spreadsheet and filter out all the rows that have a cell with the
background color of red? The colored cells are located in different columns.
I need to filter/sort and get rid of the rows that have no color at all.
(that is - the colored cells are of importance)

I tried to seach for similar problems on here but found none. Any help
would be appreciated.

Thanks,
Terrel
 
T

Tom Ogilvy

Sub DeleteRows()
Dim lastrow as long, lastColumn as Long
Dim i as Long, j as Long
Dim bColored as Boolean
LastRow = 500
LastColumn = 26
for i = lastrow to 2 step -1
bcolored = False
for j = 1 to lastcolumn
if cells(i,j).Interior.ColorIndex <> xlNone then
bcolored = True
exit for
end if
Next
if not bColored then
rows(i).Delete
End if
Next
End if

Set values for LastRow and LastColumn
 
R

Roman

Hi Terrel,
you can use user defined function like this.

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

to get the colorindexes of cells and then use filter on the colorindex
numbers.
 
L

LoboNetwork

Thanks!
This is good.. But What if I dont want to delete the uncolored ones... hmm
 
L

LoboNetwork

I can work around the not-deleting by making a copy of the sheet and then
running the code on it.

The code you provided was of great help. Thank you again.
 
T

Tom Ogilvy

Sub DeleteRows()
Dim lastrow as long, lastColumn as Long
Dim i as Long, j as Long
Dim bColored as Boolean
LastRow = 500
LastColumn = 26
Rows.Hidden = False
for i = lastrow to 2 step -1
bcolored = False
for j = 1 to lastcolumn
if cells(i,j).Interior.ColorIndex <> xlNone then
bcolored = True
exit for
end if
Next
if not bColored then
rows(i).Hidden = True
End if
Next
End if

Hides the uncolored rows.
 
H

haznavy

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.

The user defined function as written only places a single integer in the
cell selected. Any help would be greatly appreciated.

haznavy
 
D

Dave Peterson

Insert a new column F.

Select F4:F900
Type this:
=tellmecolor(e4)
and hit ctrl-enter to fill the selection with the formula (excel will adjust the
row number).
 
R

Rick Rothstein \(MVP - VB\)

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.
Click to expand...

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
 
S

smierau

MS 2007 allows you to filter by cell color, but it's not working correctly.
When I filter on a selected color the system doesn't filter all of the cells
with the color and it displays some cells with a totally different color.
Any ideas?
 
R

Ron de Bruin

Hi smierau

If you want send me a workbook with this problem private and
I will create a bug report if I can reproduce it
 
Top